Adding rows to a table with identity column

Many commercial products allow using auto incrementable columns, i.e. columns that form their values automatically when adding new rows. Such columns are widely used as primary keys in tables by virtue of uniqueness of these values. A typical example of that column is sequential counter, which generates a value greater by 1 than previous value (the value obtained when adding previous row).

Below, there is example of creating a table with IDENTITY column (code) in MS SQL Server.

CREATE TABLE Printer_Inc
(
code int IDENTITY(1,1) PRIMARY KEY ,
model varchar (4) NOT NULL ,
color char (1) NOT NULL ,
type varchar (6) NOT NULL ,
price float NOT NULL
);

Autoincrementable column is defined trough IDENTITY (1, 1) function where the first parameter (seed value) is the value to be assigned to the first row in the table, and the second is the increment to add to the seed value for successive rows in the table. So, in that example, the first inserted row obtains in the code column the value of 1, the second row - 2 and so on.

Since the value in the code column is formed automatically, the statement

INSERT INTO Printer_Inc
VALUES (15, 3111, 'y', 'laser', 599);

raises error, even though the table has not a row with the value of 15 in the code column. Thus we will not include that column when adding the row to the table just as we do when using default value, i.e.

INSERT INTO Printer_Inc (model, color, type, price)
VALUES (3111, 'y', 'laser', 599);

As a result, the information about model 3111 for color laser printer priced $2599 will be added to the Printer_Inc table. The value in the column code is 15 only accidentally. In the most cases, the specific value in an identity column is of no concern for this value has no sense as a rule; the first moment is uniqueness of the value.

Nevertheless, there are cases where a specific value needs to be inserted into autoincrementable columns. This takes place, for example, when existing data must be transferred into a new structure. In so doing, these data are in a “one-to-many” relationship from the “one " side. Thus, arbitrary values are not allowed here. On the other hand, we want to use autoincrementable field later.

In the absence of autoincrementable columns in the SQL Standard, a single approach does not exist. Here, the realization in MS SQL Server. The statement

SET IDENTITY_INSERT < table name > { ON | OFF };

turn off (ON value) or on (OFF value) auto increment use. Thus, to add a row with the value of 15 in the code column, we may write

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model, color, type, price)
VALUES (15, 3111, 'y', 'laser', 599);

Note that column list is necessary in this case, i.e. we can neither write:

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc
VALUES (15, 3111, 'y', 'laser', 599);

nor

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(model, color, type, price)
VALUES (3111, 'y', 'laser', 599);

In the last case, missing value in the code column cannot be inserted automatically because auto increment is turned off.

It should be noted that numbering would be continued from the value of 16 if the value of 15 were the maximum in the code column. Clearly if the auto increment will be turned on:

SET IDENTITY_INSERT Printer_Inc OFF;

At last, let us consider an example addition the data from the Product table to the Product_Inc table while conserving the values in the code column:

SET IDENTITY_INSERT Printer_Inc ON;
INSERT INTO Printer_Inc(code, model,color,type,price)
SELECT * FROM Printer;

The following should be said of autoincrementable columns. Let the last value in the code column is 16. Then the row with that value is deleted. What is the value then in this column after adding a new row? Correctly, 17, i.e. the last value of counter is conserved despite of deletion of the row with the value of 16. So, the numbering will not be sequential after deletion and addition of rows. This is another reason for inserting the row with a given (missed) value in the autoincrementable column.

Let’s consider a table with a single auto-increment column (SQL Server):

CREATE TABLE test_Identity(
    id int IDENTITY(1,1) PRIMARY KEY
);

How to insert rows into above table? If you’ll try to not specify a value

INSERT INTO test_Identity VALUES();

or to use DEFAULT keyword

INSERT INTO test_Identity VALUES(DEFAULT);

you’ll not succeed  - error is answer.

Surely, we could insert specific value when turning the counter off:

SET IDENTITY_INSERT test_Identity ON;  
INSERT INTO test_Identity(id) VALUES(5);  
SELECT * FROM test_Identity;

but this has no sense for us.

The task we are trying to solve is being worded as: “How to insert namely successive values of a counter into a table?” It turns out that answer is obvious, it lies in Standard syntax:

SET IDENTITY_INSERT test_Identity OFF;
INSERT INTO test_Identity DEFAULT VALUES;

It is hardly imagined that you’ll use DEFAULT VALUES in other situations, as existence of primary key allow you to use that clause only once when inserting default values for all the columns of  table. But here we can rerun the above statement as many times as many successive values we need in a table.

But what about other DBMS?

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?

CREATE TABLE test_Identity (
id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (id)
);

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:

insert into test_Identity values();

or so

insert into test_Identity values(default);

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:

insert into test_Identity values(8);
insert into test_Identity values(default);
select * from test_Identity;

PostgreSQL

create table identity_table(id serial primary key);

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

insert into identity_table default values;
insert into identity_table values(default);
insert into identity_table(id) values(default);
select * from identity_table;

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

insert into identity_table(id) values(5);

and proceed getting auto-incremental values

insert
into identity_table values(default);

then numbering will not be proceeded on the base of maximal value, but from the last one generated:

With so doing, when 5 will be obtained as sequential value, we get error of violation of primary key constraint:

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.

create table identity_table_wo(id serial);
insert into identity_table_wo(id) values(default);  
insert into identity_table_wo(id) values(2);  
insert into identity_table_wo(id) values(default),(default);  
select * from identity_table_wo;

How to reset auto increment values to avoid gaps (MySQL)?

Let’s take the table considered on the previous page and insert into it 3 rows.

CREATE TABLE test_Identity (
     id int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY  (id)
    );
    INSERT INTO test_Identity VALUES(),(),();
    SELECT * FROM test_Identity;

If we shall delete the last row, numbering will proceed not with 3, but with 4. I.e. last value of the counter is kept and used at the subsequent addition of rows:

delete from test_Identity where id=3;
INSERT INTO test_Identity VALUES();
SELECT * FROM test_Identity;

There is a question: " Whether is it possible to make numbering proceeding from last available value? "
Apart from a question about what it is necessary for, the answer is - possible. But this value of the counter should be set manually. So,

delete from test_Identity where id=4;
ALTER TABLE test_Identity AUTO_INCREMENT = 3;
INSERT INTO test_Identity VALUES(),(),();
SELECT * FROM test_Identity;