SQL Auto Increment
As we all know, In SQL for unique identification, we assign a column with the primary key. But in some tables, we find difficult to differentiate a column as a unique identifier to set the primary key.
So, to over come this problem, SQL server came up with a solution of providing unique keys to every record. Auto increment feature in SQL works for assigning the primary key for a numerical value for every record automatically as it is inserted.
This Auto increment feature is supported by all the databases like SQL Server, MS Access, Oracle, MySQL and PostgreSQL.
Auto Increment feature can be implemented in various servers.
- SQL server
- MySQL
- PostgreSQL
- MS Access.
- Oracle
1.SQL auto Increment:
In the SQL Server the Syntax goes as below:
IDENTITY (Start value, increment value)
Parameters:
- Start value: We should mention the starting value, which w would like to use.
- Increment value: We should mention a value by which we want to increment the key for the next records.
Example:
Let’s create an employee table having Employee name, ID, Salary, Age as the columns for the table. Using the Auto – increment feature, we will assign the employee IDs for every Employee Automatically.
For using this feature we should provide the code with the starting value and the increment value. So, for this example we give the starting value as 3201 and increment value as 1.
We need to mention Auto increment feature during the creation of the table.
create table employee ( employee_ID int IDENTITY (3201, 1) PRIMARY KEY, employee_name varchar(20), employee_Salary int, employee_age int);
Now we need to insert the records into the table. This is done using the INSERT command, a Data Manipulation Language command.
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dhruv’, 100000, 20) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Krish’, 150000, 19) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dany’, 130000, 21) ;
To view all the records,
select * from employee;
| Employee_ID | Employee_name | Employeee_Salary | Employee_Age |
| 3201 | Dhruv | 100000 | 20 |
| 3202 | Krish | 150000 | 19 |
| 3203 | Dany | 130000 | 21 |
2. MySQL Auto Increment:
In MySQL Server also the auto increment feature works the same. The variation arises at the syntax during the implementation.
In here, the default starting value and default increment value will be 1 and 1 respectively.
Example:
First, we will create a student table having Student_name, Student_ID, Student_marks as the fields.
If we mention the Auto increment feature during the table creation, this activates the default values.
create table student ( Student_ID int AUTO_INCREMENT PRIMARY KEY, Student_name varchar(20), Student_marks int , Student_age int) ;
Now, we should insert the records into the tables using INSERT command.
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dhruv’, 95, 20) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Krish’, 80, 19) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dany’, 97, 21) ;
select * from Student;
This command will display all the records from the table.
| Student_ID | Student_name | Student_marks | Student_age |
| 1 | Dhruv | 95 | 20 |
| 2 | Krish | 80 | 19 |
| 3 | Dany | 97 | 21 |
NOTE POINTS:
- For customizing the starting value, we can use the ALTER TABLE command.
Example:
ALTER TABLE Student AUTO_INCREMENT = customized value;
The customized value over here is the new starting value for the table student.
- For changing the Auto increment interval value from 1 to any other customized value/new value, we write the following command using auto_increment_increment.
Example:
mysql>
SET @@ auto_increment_increment = new_value_for_interval;
3.PostgreSQL Auto Increment:
In PostgreSQL, we use SERIAL keyword for implementing the auto increment feature.
Example:
At first, we need to create a student table using the SERIAL Keyword. Consider Student_ID, Student_marks, Student_Name, Student_age as the fields in the student table.
create table student ( student_ID int SERIAL PRIMARY KEY, Student_Name varchar(20), Student_marks int, Student_age int) ;
Now we should insert the rows into it without mentioning the Student_ID value, as we allotted the Auto increment feature to the Student_ID field of the student table.
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dhruv’, 95, 20) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Krish’, 80, 19) ;
insert into Student (Student_name, Student_marks, Student_age ) values ( ‘Dany’, 97, 21) ;
select * from student;
This command will display all the records from the table.
| Student_ID | Student_name | Student_marks | Student_age |
| 1 | Dhruv | 95 | 20 |
| 2 | Krish | 80 | 19 |
| 3 | Dany | 97 | 21 |
4. MS Access Auto Increment:
The Auto increment feature is also implemented in MS Access using the AUTOINCREMENT keyword. The default starting value and the default increment value is 1 and 1 respectively.
Example:
Create a table with table name as Employee having employee_ID, employee_Name, employee_Salary, employee_Age as columns.
create table Employee ( employee_ID number AUTOINCREMENT PRIMARY KEY, employee_Name varchar(20), employee_Salary number, employee_Age number);
Now, we should insert the rows into the table. There is no need of specifying the employee_ID value while inserting as here we are using the auto increment feature.
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dhruv’, 100000, 20) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Krish’, 150000, 19) ;
insert into employee (employee_name, employee_Salary, employee_age ) values ( ‘Dany’, 130000, 21) ;
select * from Employee;
| Employee_ID | Employee_name | Employeee_Salary | Employee_Age |
| 1 | Dhruv | 100000 | 20 |
| 2 | Krish | 150000 | 19 |
| 3 | Dany | 130000 | 21 |
As we can observe in the above table, the employee_ID column is having ID’s starting from 1 and incremented by 1 for each row by default. We can change the starting value and increment value also. The syntax for implementing this is,
AUTOINCREMENT ( starting value, increment value)
5.Oracle Auto Increment:
We should make the Auto increment field along with the sequence object which then generates a number sequence.
The syntax in the oracle server works as below:
minivalue 1
start with 1
increment by 1
cache 10;
Example:
create students_seq
minvalue 10
start with 1001
increment by 1
cache: 20;
cache: 20;
To insert the records into the students table, we will use the nextval function. The main use of this function is that, we can retrieve the post value from the students_seq sequence.
insert into Students( student_ID, student_Name) values (students_seq.nextval, “Anshu”);
insert into Students( student_ID, student_Name) values (students_seq.nextval, “Dhruv”);
insert into Students( student_ID, student_Name) values (students_seq.nextval, “Daksh”);
Output:
| Student_ID | Student_Name |
| 1001 | Anshu |
| 1002 | Dhruv |
| 1003 | Daksh |