Home » How to use Auto-Increment in SQL

How to use Auto-Increment in SQL

by Online Tutorials Library

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

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:

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.

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

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:

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:

Syntax of Auto-Increment in Oracle

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

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:

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:

Student_ID Student_Name Student_Age Student_Subject Student_Obtained_Marks
1001 Ram 20 Chemistry 99

Next TopicSQL Languages

You may also like