Adding rows to a table with identity column page 3
MySQL does not support DEFAULT VALUES clause. You can insert a row with default values into a table by another standard way, using DEFAULT keyword for each table column - VALUES(DEFAULT, DEFAULT, ...). But what about insertion of sequential value of counter into a single auto-increment column?
After executing the both of these statements, we'll obtain:
Note that when inserting specific value into auto-increment column (this can be done with ordinary INSERT statement), which will be greater than maximal value among existing ones in a table, the next increment will start from this inserted one. For example:
You can use some of above ways to insert sequential counter's values:
However, if you'll insert a specific value (which is above the maximal value that has been achieved by the counter):
With so doing, when 5 will be obtained as sequential value, we get error of violation of primary key constraint:
ERROR: duplicate key value violates unique constraint "identity_table_pkey"
DETAIL: Key (id)=(5) already exists.
But if the table has no primary key on auto-increment column, we'll get duplicate values and numbering will be proceeded further. Below is script for illustrating this behaviour.