Auto Increment Column in SQL

We can create auto increment column in SQL which insert the value in the column automatically.

Syntax is:-

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE IDENTITY ([START WITH],[INCREMENT BY]),
COLUMN_NAME DATA_TYPE,
.....
);

Here:-

START WITH is the values in number from which you need to start the sequence.

INCREMENT BY is the value in number by which you need to increment the sequence.


While creating IDENTITY column, make sure you have specified number families data type like int, number, decimal etc.


Let's have an example:-

CREATE TABLE EMPLOYEE
(
EMPNO INT IDENTITY (1,1),
EMPNAME VARCHAR(20),
SALARY INT,
DEPTNO INT
);

When you will insert a value into the table, it will insert 1 in the EMPNO column and further increment by 1.

When you insert a value into the table you would need to mention the column list. If you want to insert data into
the above table then you will have to use the statement something like below:-

INSERT INTO EMPLOYEE (EMPNAME,SALARY,DEPTNO)
VALUES ('CHANCHAL',1000,10);




Read Also:-  Auto Increment Column in Greenplum