SQL AUTO INCREMENT
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 every time a new record is inserted.
We can create an auto-increment field in the table.
Syntax for MySQL
The following SQL statement defines the "ID" column in the "Persons" table as an auto-increment primary key field:
(
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 auto-increment tasks.
By default, the starting value of AUTO_INCREMENT is 1 and increments by 1 for each new record.
To start the AUTO_INCREMENT sequence with a different value, use the following SQL syntax:
To 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):
VALUES ( 'Lars','Monsen')
The above SQL statement will insert a new record in 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:
(
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-increment tasks.
In the above example, the starting value of IDENTITY is 1 and is incremented by 1 for each new record.
Tips: To specify that the "ID" column starts with 10 and increments by 5, please 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):
VALUES ('Lars','Monsen')
The above SQL statement will insert a new record in 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:
(
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-increment tasks.
By default, the starting value of AUTOINCREMENT is 1 and increments by 1 for each new record.
Tips: To specify that the "ID" column starts with 10 and increments by 5, please 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):
VALUES ('Lars','Monsen')
The above SQL statement will insert a new record in 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 a little more complicated.
You must create the auto-increment field from a sequence object (which generates a sequence of numbers).
Please use the following CREATE SEQUENCE syntax:
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
The above code creates a sequence object named seq_person, which starts with 1 and increments by 1. This object caches 10 values to improve performance. The cache option specifies how many sequence values to store in order to improve access speed.
To insert a new record in the "Persons" table, we must use the nextval function (which retrieves the next value from the seq_person sequence):
VALUES (seq_person.nextval,'Lars','Monsen')
The above SQL statement will insert a new record in 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".
