SQL Auto Increment

Filed Under: SQL

Sometimes we don’t have unique identifiers in the table to create a primary key. In this case, we can take help from SQL to have a special column for the primary key, whose values will be automatically incremented when a new row is inserted.

SQL Auto Increment

SQL Auto-increment generates a unique incrementing number when a new row is inserted. Usually, the incrementing number is used for the primary key.

We will try to understand how the auto-increment feature is used with the below databases.

  • MySQL
  • SQL Server
  • PostgreSQL

MySQL Auto Increment

Syntax:


CREATE TABLE table_name (
column1 int NOT NULL AUTO_INCREMENT,
column2 datatype,
columnN datatype
);

In the syntax above, the keyword AUTO_INCREMENT specifies that the corresponding column value has to be incremented by 1.

In MySQL, the value is incremented by default by 1 and starts from 1. If you want to start the value from some other number instead of 1, you can use the following command.


ALTER TABLE table_name AUTO_INCREMENT = new_value;

The new_value will as the new start value for auto increment. Let us try to create a table with auto increment in MySQL.


CREATE TABLE Student (
stdid int NOT NULL AUTO_INCREMENT,
stdFirstName varchar(255),
stdLastName varchar(255),
Age int,
PRIMARY KEY (stdid)
);

We are creating a Student table using the above query and the primary key is auto incremented using AUTO_INCREMENT keyword.

Post-execution of the above mentioned, the table properties will look as shown below.

MySQL Auto Increment

MySQL Auto Increment

If you check the “Extra” column, for stdid, auto_increment is mentioned in Extra column.

SQL Server IDENTITY for Auto Increment

Syntax:


CREATE TABLE table_name (
column1 int IDENTITY(1,1) PRIMARY KEY,
column2 datatype,
columnN datatype
);

In the syntax above, the keyword IDENTITY specifies that the corresponding column value has to be incremented.

In the above-mentioned example, the count will start from 1 and will be incremented by 1.

If we want to have the sequence started from 10 and increment it by 5, following will be the syntax for the same.

IDENTITY(10,5);

Let us try to create a table with auto increment in SQL Server.


CREATE TABLE Student (
stdid int IDENTITY(1,1) PRIMARY KEY,
stdFirstName varchar(255),
stdLastName varchar(255),
Age int
);

Insert data into the Student table using the below-mentioned query.


INSERT INTO [dbo].[Student]
([stdFirstName]
,[stdLastName]
,[Age]) VALUES ('First1','Last1',1);

When we insert the data, we are not inserting the value for “stdid” but the value will be auto provided by SQL Server as shown in the image below.

SQLServer Auto Increment

SQLServer Auto Increment

PostgreSQL SERIAL for Auto Increment

Syntax:


CREATE TABLE table_name (
column1 datatype SERIAL PRIMARY KEY,
column2 datatype,
columnN datatype
);

In the syntax above, we can see that the keyword SERIAL is used for specifying that the corresponding column will have auto-incremented values.

Let us try to create a table with auto increment in PostgreSQL.


CREATE TABLE Student (
stdid SERIAL PRIMARY KEY,
stdName varchar(255)
);

After executing the above-mentioned query, “stdid” will have the auto-increment feature.

Conclusion

SQL Auto Increment is a very useful feature when we don’t have primary key columns in the table data. Notice that every database vendor has their own keyword to implement auto increment feature.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages