SQL Tutorial SQL Advanced SQL Database SQL References

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 statement examples

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 also. For example - in IDENTITY(100, 5), 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.

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.

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.

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', 25, 3000);

The above SQL code will assign the next number from the seq_Employee sequence.


5