AUTO_INCREMENT - Creates a unique ID when inserting new record into the table.

Syntax for MySQL

The following SQL statement creates the auto increment for the column "P_Id" in table "Persons" ..

CREATE TABLE Persons (
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

By default, AUTO_INCREMENT is 1 and with each new record is increased by 1.

To specify a different starting value of the AUTO_INCREMENT, use the following SQL query:

1

ALTER TABLE Persons AUTO_INCREMENT=100

1
2

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

Syntax for SQL Server

The following SQL query creates the auto increment for the column "P_Id" in table "Persons".

1
2
3
4
5
6
7

CREATE TABLE Persons (
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

In MS SQL Server IDENTITY keyword creates an auto increment.

By default, the IDENTITY is 1 and with each new record is increased by 1.

1
2

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

The syntax for Access

The following SQL query creates the auto increment for the column "P_Id" in table "Persons".

1
2
3
4
5
6
7

CREATE TABLE Persons (
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

In MS Access AUTOINCREMENT keyword creates auto increment.

The default is 1 AUTOINCREMENT and with each new record is increased by 1.

1
2

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')