SQL AUTO INCREMENT field

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


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.


The syntax for MySQL

The following SQL statement defines the "ID" column in the "Peoples" 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 AUTO_INCREMENT keywords to perform auto-incremental tasks.

By default, AUTO_INCREMENT starts at 1 and each new record increases by 1.

To start the sequence with AUTO_INCREMENT values, use the following SQL syntax: 

ALTER TABLE Persons AUTO_INCREMENT=100

To insert a new record into the Persons table, we don't need to specify a value for the ID column (auto-add a unique value): 

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

The SQL statement above inserts a new record into the "Persons" table. T he ID bar will get a unique value. The FirstName column is set to Lars and the LastName column is set to Monsen.


The 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 auto-incremental tasks.

In the example above, IDENTITY starts at 1 and each new record increments are 1.

Tip: Specify that the "ID" column starts with 10 and increments 5, changing the identity to IDENTITY (10,5).

To insert a new record into the Persons table, we don't need to specify a value for the ID column (auto-add a unique value): 

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

The SQL statement above inserts a new record into the Persons table. T he ID bar will get a unique value. The FirstName column is set to Lars and the LastName column is set to Monsen.


The 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 auto-incremental tasks.

By default, AUTOINCREMENT starts at 1, with each new record increasing by 1.

Tip: Specify that the "ID" column starts with 10 and increments 5, changing auto-increment (autoincrement) to auto-increment (105) (AUTOINCREMENT (10,5)).

To insert a new record into the Persons table, we don't need to specify a value for the ID column (auto-add a unique value): 

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

The SQL statement above inserts a new record into the Persons table. T he ID bar will get a unique value. The FirstName column is set to Lars and the LastName column is set to Monsen.


Syntax for Oracle

In Oracle, the code is a bit complicated.

You must use the sequence object, which generates a sequence of numbers, to create an auto-increment field.

Use the following CREATSEQUENT syntax: 

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

The code above creates a sequence seq_pean called a sequence object that starts with 1 and increments by 1. T his object caches 10 values to improve performance. The cache option specifies how many sequence values to store to improve access speed.

To insert a new record into the "Persons" table, we must use the nextval function, which retrieves the next value seq_hor the sequence of the data: 

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

The SQL statement above inserts a new record into the "Persons" table. T he ID column assigns the seq_person number from the list sequence. T he FirstName column is set to Lars and the LastName column is set to Monsen.