SQL AUTO INCREMENTFields

Auto-increment generates a unique number when a new record is inserted into a table.

AUTO INCREMENT Field

We usually want to automatically create the value of the primary key field whenever a new record is inserted.

We can create an auto-increment field in a table.

Syntax for MySQL

The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:

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

MySQL uses the AUTO_INCREMENT keyword to perform the auto-increment task.

By default, the starting value for AUTO_INCREMENT is 1, and it increments by 1 for each new record.

To make the AUTO_INCREMENT sequence start with a different value, use the following SQL syntax:

ALTER TABLE Persons AUTO_INCREMENT=100

To insert a new record into the "Persons" table, we do not have to specify a value for the "ID" column (a unique value will be added automatically):

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

The SQL statement above inserts a new record into the "Persons" table. The "ID" column will be assigned a unique value. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

Syntax for SQL Server

The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:

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

MS SQL Server uses the IDENTITY keyword to perform the auto-increment task.

In the example above, the starting value for IDENTITY is 1, and it increments by 1 for each new record.

Tip:To specify that the "ID" column starts at 10 and increments by 5, change identity to IDENTITY(10,5).

To insert a new record into the "Persons" table, we do not have to specify a value for the "ID" column (a unique value will be added automatically):

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

The SQL statement above inserts a new record into the "Persons" table. The "ID" column will be assigned a unique value. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

Syntax for Access

The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:

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

MS Access uses the AUTOINCREMENT keyword to perform the auto-increment task.

By default, the starting value for AUTOINCREMENT is 1, and it increments by 1 for each new record.

Tip:To specify that the "ID" column starts at 10 and increments by 5, change autoincrement to AUTOINCREMENT(10,5).

To insert a new record into the "Persons" table, we do not have to specify a value for the "ID" column (a unique value will be added automatically):

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

The SQL statement above inserts a new record into the "Persons" table. The "ID" column will be assigned a unique value. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".

Syntax for Oracle

In Oracle, the code is slightly more complex.

You must create the auto-increment field using a sequence object (which generates a sequence of numbers).

Use the following CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

The code above creates a sequence object named seq_person that starts at 1 and increments by 1. This object caches 10 values to improve performance. The cache option specifies how many sequence values are stored to improve access speed.

To insert a new record into the "Persons" table, we must use the nextval function (which retrieves the next value from the seq_person sequence):

INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')

The SQL statement above inserts a new record into the "Persons" table. The "ID" column will be assigned the next number from the seq_person sequence. The "FirstName" column will be set to "Lars", and the "LastName" column will be set to "Monsen".


Other Extensions