SQL AUTO INCREMENT for Different Database Systems

MySQL: Syntax

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

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:

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:

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":

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:

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":

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:

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:

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:

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