SQL AUTO INCREMENT

December 30, 2022

AUTO-INCREMENT in SQL is used to auto-generate a new record inserted for a specific column in the table. The AUTO-INCREMENT is often entitled with the column that is PRIMARY KEY. Different databases use different keywords to apply auto incrementation on a column. Below are different syntaxes as per the databases.

Syntax of AUTO-INCREMENT in MySQL –

CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT, column2 data type, column3 data type, ....., PRIMARY KEY (column1));

By default, the starting value for AUTO_INCREMENT is 1, and the incrementation will be by ‘1’. To start the AUTO_INCREMENT sequence start with another value, we can use ALTER keyword.

Syntax of AUTO-INCREMENT in SQL Server –

CREATE TABLE table_name ( column1 datatype IDENTITY(1,1) PRIMARY KEY, column2 data type, column3 data type, ....., columnN data type;

SQL Server uses the IDENTITY keyword to perform an auto-increment on the column. The (1,1) in the above syntax shows the starting value for IDENTITY is 1, and it will be incremented by 1 for the new records.

Example – Create a new table as Teachers with columns (ID, Name, course) and add auto-increment values for the ID.

CREATE TABLE Teachers ( ID int IDENTITY(1,1) PRIMARY KEY, Name varchar(255) NOT NULL, Course varchar(255));

Now the Teacher’s table has been created with auto incrementation on the ID. Insert the values in the tables as shown.

INSERT INTO Teachers (Name,Course) VALUES ('Lauren','Science'); INSERT INTO Teachers (Name,Course) VALUES ('Wilson','Maths'); INSERT INTO Teachers (Name,Course) VALUES ('Manisha','Computer');

The above three records have been inserted in the table without specifying the ID as it has been identified to be in auto incrementation. Executing the SELECT Statement to view the Teacher’s table –

ID	Name	Course
1	Lauren	Science
2	Wilson	Science
3	Manisha	Maths

Here when we inserted the values we didn’t mention values for ID, but here in the output, we have auto-generated incremented values for the ID column.