Code has been added to clipboard!

SQL AUTO INCREMENT for Different Database Systems

Reading time 3 min
Published Aug 9, 2017
Updated Oct 3, 2019

SQL AUTO INCREMENT Field: Main Tips

  • This field permits you to generate a unique digit when a data record is written to a table.
  • This is useful when you want the primary key field value to be generated each time we insert a new record.

MySQL: Syntax

In the code examples below we specify the column "ID" to be auto-incremented in the table "Person":

Example
CREATE TABLE Person (
    ID int NOT NULL AUTO_INCREMENT,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int,
    PRIMARY KEY (ID)
);

Auto-incrementation is started by a keyword AUTO_INCREMENT in MySQL.

The keywords value starts with 1 and then gets incremented by 1 more for every record.

In the code example below we start the sequence with a different value:

Example
ALTER TABLE Person AUTO_INCREMENT=100;

When writing another data record to the table "Person", we do not need to define a value for the column "ID" because it is going to be added automatically:

Example
INSERT INTO Person (FName,LName)
VALUES ('Bob','Bonbon');

Syntax for SQL Server

In the code examples below we specify the column "ID" to be auto-incremented in the table "Person":

Example
CREATE TABLE Person (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int
);

Auto-incrementation is started by a keyword IDENTITY in MS SQL Server.

The keywords value starts with 1 and then gets incremented by 1 more for every record.

When defining the column to start with 20 and get incremented by 10 write it like this IDENTITY(20,10).

When writing another data record to the table "Person", we do not need to define a value for the column "ID" because it is going to be added automatically:

Example
INSERT INTO Person (FName,LName)
VALUES ('Bob','Bonbon');

Syntax for Access

In the code examples below we specify the column "ID" to be auto-incremented in the table "Person":

Example
CREATE TABLE Person (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int
);

Auto-incrementation is started by a keyword IDENTITY in MS SQL Server.

The keywords value starts with 1 and then gets incremented by 1 more for every record.

When defining the column to start with 20 and get incremented by 10 write it like this IDENTITY(20,10).

When writing another data record to the table "Person", we do not need to define a value for the column "ID" because it is going to be added automatically:

Example
INSERT INTO Person (FName,LName)
VALUES ('Bob','Bonbon');

Syntax for Oracle

In oracle it's a bit different. An auto-incrementation field is needed to be created containing a sequence object which will produce a digit sequence.

In the code example below we use CREATE SEQUENCE:

Example
CREATE SEQUENCE seq_persons
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

The example above produces an seq_persons sequence object which beings with 1 and an incrementation of 1. Then it caches 10 values to have better performance.

When writing a new data record to the table "Person" the nextval function is needed to be used which brings back the following value in seq_persons sequence:

Example
INSERT INTO Person (ID,FName,LName)
VALUES (seq_persons.nextval,'Bob','Bonbon');