What is AUTO INCREMENT in SQL?
FREE
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.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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:
| Employee_ID | name |
|---|---|
| 1 | Ram |
| 2 | Shyam |
| 4 | Mohan |
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:
| Employee_ID | name |
|---|---|
| 1 | Ram |
| 2 | Shyam |
| 4 | Mohan |
| 5 | Dinesh |
| 6 | Mahesh |
Note: The auto-increment column value initially starts at 1. You can alter the auto-increment start value if you'd like using the syntax below.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Learn More
Conclusion
- AUTO INCREMENT keyword in SQL can update the corresponding column record.
- It is heavily useful when maintaining serial number-like records.
- The AUTO INCREMENT column value initially starts at 1. You can alter the auto-increment start value if you'd like.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more