What is AUTO INCREMENT in SQL?

AUTO INCREMENT is a powerful keyword in SQL. It is used to generate auto-incremented data, such as serial numbers, every time. Applying the auto-increment functionality to a field in SQL will automatically produce and deliver a unique value for each record you insert into the table. This column is frequently used as the primary key, and each item you add must have a different value. It can also be used to create columns with UNIQUE constraints.

How to Set and Use AUTO INCREMENT in SQL?

Let us explore how to set and use auto increment in some popular SQL databases:

MySQL

In MySQL, you can use the AUTO_INCREMENT attribute to set a column as an auto-incrementing column. Here is the syntax:

Syntax:

In this example, column1 is set as the auto-incrementing primary key. Here's an example of how to insert data into this table:

Query:

MySQL will automatically generate a unique value for column1 when the new record is inserted.

SQL Server

In SQL Server, you can use the IDENTITY property to set a column as an auto-incrementing column. Here is the syntax:

Syntax:

In this example, column1 is set as the auto-incrementing primary key. Here's an example of how to insert data into this table:

Query:

SQL Server will automatically generate a unique value for column1 when the new record is inserted.

MS Access

In MS Access, you can use the AUTOINCREMENT attribute to set a column as an auto-incrementing column. Here is the syntax:

Syntax:

In this example, column1 is set as the auto-incrementing primary key. Here's an example of how to insert data into this table:

Query:

MS Access will automatically generate a unique value for column1 when the new record is inserted.

Oracle

In Oracle, you can use the SEQUENCE object to set a column as an auto-incrementing column. Here is the syntax:

Syntax:

In this example, sequence_name is the name of the sequence object used to generate unique values for column1. Here's an example of how to insert data into this table:

Query:

Oracle will automatically generate a unique value for column1 using the sequence object when the new record is inserted.

PostgreSQL

In PostgreSQL, you can use the SERIAL data type to set a column as an auto-incrementing column. Here is the syntax:

Syntax:

In this example, column1 is set as the auto-incrementing primary key using the SERIAL data type. Here's an example of how to insert data into this table:

Query:

When you insert a new row into the table, the column1 value will be automatically generated and incremented.

How to Add AUTO INCREMENT Column in an Existing Table in SQL?

To add the AUTO_INCREMENT feature to a column, we can alter the table and update the column attributes.

Consider the example below, we first create a table that does not have any auto-increment field.

Our existing table looks like this:

Now let's say we want the Employee_ID column to inherit the property of AUTO_INCREMENT for upcoming records, i.e., we want it to increase automatically for every new record added. We need to alter the table and the column to achieve it.

Now let's add a couple of new records to understand how it affects the column.

Now the table looks like this:

Learn More

• Advanced Concepts of SQL