loading..
Русский    English
16:16
go

Adding rows to a table with identity column page 3

MySQL

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?

  1. CREATE TABLE test_Identity (
  2.  id int(11) NOT NULL AUTO_INCREMENT,
  3.   PRIMARY KEY  (id)
  4. );
It's very simple. It turns out that intuitive ways, which we unsuccessfully tried to use in the case of SQL Server, will work, namely so: 

  1. INSERT INTO test_Identity VALUES();
or so

  1. INSERT INTO test_Identity VALUES(DEFAULT);

After executing the both of these statements, we'll obtain:

id
1
2

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:

  1. INSERT INTO test_Identity VALUES(8);
  2. INSERT INTO test_Identity VALUES(DEFAULT);
  3. SELECT * FROM test_Identity;

id
1
2
8
9

PostgreSQL

  1. CREATE TABLE identity_table(id serial PRIMARY KEY);

You can use some of above ways to insert sequential counter's values:

  1. INSERT INTO identity_table DEFAULT VALUES;
  2. INSERT INTO identity_table VALUES(DEFAULT);
  3. INSERT INTO identity_table(id) VALUES(DEFAULT);
  4. SELECT * FROM identity_table;

id
1
2
3

However, if you'll insert a specific value (which is above the maximal value that has been achieved by the counter): 

  1. INSERT INTO identity_table(id) VALUES(5);
and proceed getting auto-incremental values 

  1. INSERT INTO identity_table VALUES(DEFAULT);
then numbering will not be proceeded on the base of maximal value, but from the last one generated:

id
1
2
3
5
4

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.

  1. CREATE TABLE identity_table_wo(id serial);
  2. INSERT INTO identity_table_wo(id) VALUES(DEFAULT);
  3. INSERT INTO identity_table_wo(id) VALUES(2);
  4. INSERT INTO identity_table_wo(id) VALUES(DEFAULT),(DEFAULT);
  5. SELECT * FROM identity_table_wo;

id
1
2
2
3
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.