Sql Autoincrement

SQL AUTO INCREMENT Field | Simple Tutorial

AUTO INCREMENT Field

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

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

MySQL Syntax

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 of AUTO_INCREMENT is 1, and it increments by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL syntax:

ALTER TABLE Persons AUTO_INCREMENT=100

When inserting a new record into the "Persons" table, we don't need to specify a value for the "ID" column (a unique value will be automatically added):

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

The above SQL statement 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".

SQL Server Syntax

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 above example, the starting value of IDENTITY is 1, and it increments by 1 for each new record.

Note: To set the "ID" column to start at 10 and increment by 5, change identity to IDENTITY(10,5).

When inserting a new record into the "Persons" table, we don't need to specify a value for the "ID" column (a unique value will be automatically added):

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

Access Syntax

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 of AUTOINCREMENT is 1, and it increments by 1 for each new record.

Note: To set the "ID" column to start at 10 and increment by 5, change autoincrement to AUTOINCREMENT(10,5).

When inserting a new record into the "Persons" table, we don't need to specify a value for the "ID" column (a unique value will be automatically added):

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

Oracle Syntax

In Oracle, the code is a bit more complex.

You must create an auto-increment field using a sequence object (an object that generates a number sequence).

Use the following CREATE SEQUENCE syntax:

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

The above code creates a sequence object named seq_person, which starts at 1 and increments by 1. This object caches 10 values to improve performance. The cache option specifies how many sequence values should be stored for faster access.

When inserting 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 above SQL statement 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".

YouTip