SQL - AUTO INCREMENT

The SQL AUTO INCREMENT statement is used to create a unique numerical value for each additional record inserted into a table. Generally, it is used to create the numerical primary key field.

SQL AUTO INCREMENT statements

MySQL

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which auto-increment is applied on column EmpID.

CREATE TABLE Employee (
  EmpID INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

MySQL uses AUTO_INCRMENT keyword to add auto-increment feature. By default, the start value of AUTO_INCRMENT is 1, and it is increased by 1 for each new record. To change the start value in MySQL, the below SQL code can be used.

ALTER TABLE Employee AUTO_INCREMENT=50;

While inserting a new record, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

The SQL query mentioned above will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.

SQL Server

Below mentioned SQL code describes the implementation of auto-increment statement on EmpID column of Employee table in SQL server.

CREATE TABLE Employee (
  EmpID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

SQL server uses IDENTITY keyword to add auto-increment feature. In this example, the start and increment values of IDENTITY keyword are 1. The start value and increment value can be changed as required. For example - when IDENTITY(100, 5) is used, the start value will be 100, and increment value will be 5.

While inserting a new record, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

This SQL query will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.

MS Access

The below SQL code shoes the implementation of auto-increment statement on EmpID column of Employee table in MS Access server.

CREATE TABLE Employee (
  EmpID INT NOT NULL PRIMARY KEY AUTOINCREMENT,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

MS Access uses AUTOINCREMENT keyword to add auto-increment feature. By default, the start value of AUTOINCREMENT is 1, and it is increased by 1 for each new record. The start value and increment value can be changed also. By using AUTOINCREMENT(100, 5), the start value will be 100, and increment value will be 5.

Please note that, while inserting a new record, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

The above SQL query will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.

Oracle

In oracle, the auto-increment feature be achieved by creating a sequence object. The below code creates a sequence object called seq_Employee which starts at 1 and increases by 1. It also caches up to 20 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.

CREATE SEQUENCE seq_Employee
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

While inserting a new record, nextval function will be used with auto-increment field.

INSERT INTO Employee (EmpID, Name, City, Age, Salary)
VALUES (seq_Employee.nextval,'John','London', 27, 2800);

The above SQL code will assign the next number from the seq_Employee sequence to the "EmpID" column. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.