SQL AUTO INCREMENT

In this tutorial, we will learn how to use IDENTITY, AUTO_INCREMENT, SEQUENCE using SQL.

SQL AUTO INCREMENT Field

• SQL AUTO INCREMENT field allows a unique number to be generated automatically when a new record is inserted in a table.
• It is mainly used with the primary field.

Syntax for SQL AUTO INCREMENT in SQL Server

CREATE TABLE Employee(
ID INT IDENTITY(1,1) NOT NULL,
EmpName VARCHAR(255),
City VARCHAR(255),
Country VARCHAR(255),
Salary INT,
PRIMARY KEY(ID)
);

SQL Server uses the IDENTITY to perform an auto increment.

In the example given above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record is inserted in a table.

If you want to set 100 as starting value and want to set increment value by 5, then you have change IDENTITY(100,5).

To insert a new record into a table "Employee", you will not have to specify a value for the column "ID", it will automatically add unique value.

INSERT INTO Employee (EmpName,City,Country,Salary)
VALUES('Dada Thakur','Delhi','India','45000');

This INSERT statement inserts a new record into a table "Employee" and the "ID" column would be assigned a unique value.

Syntax for AUTO INCREMENT in MySQL Server

CREATE TABLE Employee(
ID INT NOT NULL AUTO_INCREMENT,
EmpName VARCHAR(255),
City VARCHAR(255),
Country VARCHAR(255),
Salary INT,
PRIMARY KEY(ID)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto increment.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record inserted in a table.

To set the initial value for AUTO_INCREMENT With the different value, the statement is as follow:

ALTER TABLE tableName AUTO_INCREMENT=value;

ALTER TABLE Employee AUTO_INCREMENT=1000;

To insert a new record into a table "Employee", you will not have to specify a value for the column "ID", it will automatically add unique value.

INSERT INTO Employee (EmpName,City,Country,Salary)
VALUES('Dada Thakur','Delhi','India','45000');

This INSERT statement inserts a new record into a table "Employee" and the "ID" column would be assigned a unique value.

Syntax for AUTO INCREMENT in Oracle

In Oracle, the code is a little bit different from MYSQL or SQL Server, etc.

CREATE SEQUENCE seq_employee
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 100;

This above code creates a sequence object called seq_employee, that starts with 1 and it will be increment by 1. And it will also cache up to 100 values for performance.

Cache option specifies how many sequence values will be stored in memory for faster access.

To Insert a new record into the table "Employee". You have to use the nextval function. This nextval function retrieves the next value from the sequence "seq_employee".

INSERT INTO Employee (ID,EmpName,City,Country,Salary)
VALUES(seq_employee,nextval,'Dada Thakur','Delhi','India','45000');

Another way for AUTO INCREMENT in Oracle 12c or later ONLY

From Oracle 12c, the IDENTITY column is now available.

Syntax for AUTO INCREMENT using IDENTITY is as follow:

CREATE TABLE tableName (
column1 int GENERATED by default on null as IDENTITY,
column2 VARCHAR2(10)
);​

or by specifying starting value and increment value and also preventing any insert into the identity column using GENERATED ALWAYS.

CREATE TABLE tableName (
column1 int GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
column2 VARCHAR2(10)
);