How to use Auto-Increment in SQL

In this SQL article, you will learn how to use Auto-Increment in Structured query Language, MySQL, Oracle, and Microsft Access.

Auto-increment is a concept in SQL which automatically generates the unique number in the field when the new row is entered into the table. This feature is generally used for the Primary Key field, where we need to create a unique value for every record.

If we use the Auto-Increment concept in an integer column and insert the value of the first tuple as 1, then the value of the second tuple is automatically generated as 2.

The SQL database system allows users to define the beginning and increment value.

Syntax of Auto-Increment in SQL

CREATE TABLE Table_Name
(
Column_Name_1 Datatype IDENTITY (Starting_Value, Increment_Value) PRIMARY KEY, 
Column_Name_2 Datatype (Size_of_Column_2),
Column_Name_3 Datatype (Size_of_Column_3),
.......,
Column_Name_N Datatype (Size_of_Column_N)
);

In the above syntax, we have to use the IDENTITY keyword that performs the concept of auto-increment in SQL.

If you want to use the concept of Auto-Increment in SQL table, you have to write the following CREATE TABLE query:

CREATE TABLE Student_Records
(
Student_ID INT IDENTITY(201, 1) PRIMARY KEY, 
Student_Name Varchar(100),
Student_Age INT,
Student_Subject Varchar(50),
Student_Obtained_Marks INT NOT NULL
);

In this query, we have created the Student_Records table with five columns. The IDENTITY keyword in the Student_ID column automatically generates the unique ID for the new student record.

So, we do not need to specify the value of Student_ID in the INSERT query because it will be inserted automatically.

The following queries insert the records of students into the above table.

INSERT INTO Student_Records (Student_Name, Student_Age, Student_Subject, Student_Obtained_Marks) VALUES (Arun, 22, Maths, 89);
INSERT INTO Student_Records (Student_Name, Student_Age, Student_Subject, Student_Obtained_Marks) VALUES (Maish, 23, Physics, 92);
INSERT INTO Student_Records (Student_Name, Student_Age, Student_Subject, Student_Obtained_Marks) VALUES (Piyush, 20, English, 76);
INSERT INTO Student_Records (Student_Name, Student_Age, Student_Subject, Student_Obtained_Marks) VALUES (Yashit, 21, Maths, 81); 

The following query shows the records of the above table in the result:

Output:

Student_ID	Student_Name	Student_Age	Student_Subject	Student_Obtained_Marks
101	Arun	22	Maths	89
102	Maish	23	Physics	92
103	Piyush	20	English	76
104	Yashit	21	Maths	81

Syntax of Auto-Increment in Ms-Access

CREATE TABLE Table_Name
(
Column_Name_1 Datatype AUTOINCREMENT PRIMARY KEY, 
Column_Name_2 Datatype (Size_of_Column_2),
Column_Name_3 Datatype (Size_of_Column_3),
.......,
Column_Name_N Datatype (Size_of_Column_N)
);

In this syntax, we have used AUTOINCREMENT keyword that performs the concept of auto-increment. MS-Access does not contain any starting and increment value for the auto-increment operation. By default, it starts with 1 and increments each new record value by 1.

If you want to use the concept of Auto-Increment in the table of Ms-Access, you have to write the following CREATE TABLE statement:

CREATE TABLE Teacher_Records
(
Teacher_ID INT AUTOINCREMENT PRIMARY KEY, 
Teacher_Name Varchar(100),
Teacher_Age,
Teacher_Subject Varchar(50),
);

To insert the new record into the Teacher_Records table, we will not have to define the value for Teacher_ID because the unique value is inserted automatically.

The following query inserts the record of teachers in the Teacher_Records table:

INSERT INTO Teacher_Records (Teacher_Name, Teacher_Age, Teacher_Subject) VALUES (Amya, 35, Maths);

Syntax of Auto-Increment in Oracle

The syntax of the oracle is tricky as compared to other databases.

CREATE SEQUENCE Name_of_Sequence
MINVALUE 1
START WITH Starting_Value
INCREMENT BY Increment_Value
CACHE 10;

In the oracle syntax, we can achieve the concept of auto-increment by creating the auto-increment field with sequence object.

If you want to use the concept of Auto-Increment in the Oracle table, you have to write the following oracle statement:

CREATE SEQUENCE Sequence_Students
MINVALUE 1
START WITH 1001
INCREMENT BY 3
CACHE 20;

In this query, we created the Sequence object that starts with 1001 and increments by 3. The Sequence_Students object can only store 20 values because the cache defines its value 20.

Suppose we want to insert the new record in the above Student_Record table, then we will have to define the nextval function which accesses the next value from Sequence_Students.

The following oracle query inserts the record of students in the Student_Records table:

INSERT INTO Student_Records (Student_ID Student_Name, Student_Age, Student_Subject, Student_Obtained_Marks) VALUES ( Sequence_Students.nextval, Ram, 20, chemistry, 99);