Auto Increment in SQL

While working with vast databases in SQL, several tables and data fields will require unique numbers. For instance, a column of the table with a PRIMARY KEY or UNIQUE constraint will always require a new number. You might be able to do it manually to a limited extent. But, when it comes to enormous databases, you might forget the last unique number you entered, or merely include the same number twice as it isn’t easy to remember everything. Besides the memory issue, providing a unique number to all the records is also a tedious task. That’s where auto increment in SQL comes in to play its part.

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.

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.

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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:

AutoIncrementInSQL_1.

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:

AutoIncrementInSQL_2

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:

AutoIncrementInSQL_3

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:

AutoIncrementInSQL_4

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's 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:

AutoIncrementInSQL_5

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:

AutoIncrementInSQL_6

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

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. If you want to learn more exciting SQL concepts, you can opt for Simplilearn’s SQL Certification Training

Happy learning!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.