What is Auto Increment in SQL and How to Set Up Auto Increment

What is Auto Increment in SQL?

The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add. However, it can also be used for the UNIQUE constraint columns.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

How to Set Up Auto Increment in SQL?

Now, since you know what the SQL auto increment field does, let’s set it up to automatically generate unique numbers for the records you enter in the database table. Several databases support SQL auto increment fields. You will be going through some primary DBMS systems and look at how to set up an auto increment column. You will have to create a Students table in all the DBMS and set auto increment in SQL.

Auto Increment in SQL: Setup for MySQL

In the MySQL server, it applies an auto increment field with the keyword AUTO_INCREMENT. By default, it starts with the number one and increases the value by one for each new record. In the example below, you will use the CREATE TABLE command to create a Students table and apply PRIMARY KEY and AUTO_INCREMENT to the ID column. After making the table, you will use the INSERT INTO command to add rows without providing the ID column’s value.

-- Creating table

CREATE TABLE Students(

    ID int PRIMARY KEY AUTO_INCREMENT,

    FirstName varchar(25) NOT NULL,

    LastName varchar(25),

    Age int

);

-- Inserting values

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);

-- Fetching values

SELECT * FROM Students;

Output:

As you can see in the output, auto increment in SQL provided the ID column with unique numbers as expected.

You can also make an auto increment in SQL to start from another value with the following syntax:

ALTER TABLE table_name AUTO_INCREMENT = start_value;

In the syntax above:

• start_value: It is the value from where you want to begin the numbering

Use the syntax to start the numbering from 100 in the Students table and then add more rows to see it in action.

ALTER TABLE Students AUTO_INCREMENT = 100;

INSERT INTO Students (FirstName, LastName, Age) VALUES ('David', 'Tiwari', 26);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Hitesh', 'Patel', 29);

SELECT * FROM Students;

Output:

Our Free Courses with Certificate

Introduction to SQL	Enroll Now!
SQL Injection For Beginners	Enroll Now!
Fundamentals of Database: What is SQL?	Enroll Now!
PostgreSQL: Become an SQL developer	Enroll Now!
Introduction to the Fundamentals of Databases	Enroll Now!

Auto Increment in SQL: Setup for SQL Server

The SQL Server uses the IDENTITY keyword to set auto increment with the following syntax while creating a table:

IDENTITY (initial_value, increment_value);

In the above syntax:

• initial_value: It is the value from where you want the numbering to begin
• increment_value: It is the increment between each auto-generated number

Let’s create the same Students table in the SQL Server and use the IDENTITY keyword to set auto increment. This time you will directly start with 1000 and have an interval of 5 between each row’s number.

-- Creating table

CREATE TABLE Students(

    ID int IDENTITY(1000, 5) PRIMARY KEY,

    FirstName varchar(25) NOT NULL,

    LastName varchar(25),

    Age int

);

-- Inserting values

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);

-- Fetching values

SELECT * FROM Students;

Output:

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Auto Increment in SQL: Setup for MS Access

The AUTOINCREMENT keyword is used to set up auto increment in MS Access. The default starting and increment values for AUTOINCREMENT are 1 and 1, respectively. However, you can easily change it while setting the auto increment with the following syntax:

AUTOINCREMENT(start_value, increment_value);

In the above syntax:

• start_value: It is the value from where you want the numbering to begin
• increment_value: It is the increment between each auto-generated number

This time you will create a Students1 table in the example below with the MS Access server and set up auto increment in SQL with the same input values. In this case, you have to set the start_value to 200 and increment_value to 4.

-- Creating table

CREATE TABLE Students1(

    ID int AUTOINCREMENT(200,4) PRIMARY KEY,

    FirstName varchar(25) NOT NULL,

    LastName varchar(25),

    Age int

);

-- Inserting values

INSERT INTO Students1 (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);

INSERT INTO Students1 (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);

INSERT INTO Students1 (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);

-- Fetching values

SELECT * FROM Students1;

Output:

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Auto Increment in SQL: Setup for Oracle

To set up auto increment in Oracle, you will have to create a sequence object first. This object is used to generate a number sequence. Here’s how you can use the CREATE SEQUENCE statement in Oracle to create a sequence object for the Students' table.

CREATE SEQUENCE seq_Students

MINVALUE 1

START WITH 20

INCREMENT BY 2

CACHE 20;

The syntax above will create a sequence object named seq_Students that starts with 20 and increases by 2 with a cache of 20 (the number of sequence values stored in memory). You will now use the built-in nextval function to add values with the auto increment feature and see it in action. Let’s create the table Students and insert the values.

CREATE TABLE Students(

  ID number(10) PRIMARY KEY,  

  FirstName varchar2(50) NOT NULL,  

  LastName varchar2(50),

  Age number(10)

);

INSERT INTO Students (ID, FirstName, LastName, Age) VALUES (seq_Students.nextval, 'Rahul', 'Kumar', 24);

INSERT INTO Students (ID, FirstName, LastName, Age) VALUES (seq_Students.nextval, 'Aakash', 'Roy', 25);

INSERT INTO Students (ID, FirstName, LastName, Age) VALUES (seq_Students.nextval, 'Nick', 'Chopra', 23);

-- Fetching values

SELECT * FROM Students;

Output:

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

Auto Increment in SQL: Setup for PostgreSQL

In PostgreSQL, the SERIAL keyword is used to set up an auto increment column. SERIAL in PostgreSQL is a data type, like BIGSERIAL and SMALLSERIAL. But it works similar to the auto increment in SQL. However, you cannot set a starting value or the increment value of your choice explicitly. Here’s how you can create a table and use the SERIAL keyword to set auto increment in PostgreSQL.

-- Creating table

CREATE TABLE Students(

    ID SERIAL PRIMARY KEY,

    FirstName TEXT NOT NULL,

    LastName TEXT,

    Age int

);

-- Inserting values

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Rahul', 'Kumar', 24);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Aakash', 'Roy', 25);

INSERT INTO Students (FirstName, LastName, Age) VALUES ('Nick', 'Chopra', 23);

-- Fetching values

SELECT * FROM Students;

Output:

Conclusion:

In this article, you learned everything about auto increment in SQL. You have gone through the use of auto increment and how to set it up in different DBMS servers. Knowing how and where to use auto increment will help you efficiently manage enormous databases, especially while working with PRIMARY KEY constraint tables. Do you have any questions related to this article on Auto Increment in SQL? In case you do, then please put them in the comments section. Our team will help you solve them. 

Happy learning!