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 the value of the primary key field to be automatically created each 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 "P_Id" column in the "Persons" table as an auto-increment primary key:
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) )
MySQL uses the AUTO_INCREMENT keyword to perform the auto-increment task.
By default, the starting value of AUTO_INCREMENT is 1, and each new record increments by 1.
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 need to specify a value for the "P_Id" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Bill', 'Gates')
The above SQL statement will insert a new record into the "Persons" table. "P_Id" will be assigned a unique value. "FirstName" will be set to "Bill", and the "LastName" column will be set to "Gates".
Syntax for SQL Server
The following SQL statement defines the "P_Id" column in the "Persons" table as an auto-increment primary key:
CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
MS SQL uses the IDENTITY keyword to perform the auto-increment task.
By default, the starting value of IDENTITY is 1, and each new record increments by 1.
To specify that the "P_Id" column starts at 20 and increments by 10, change identity to IDENTITY(20,10):
To insert a new record into the "Persons" table, we do not need to specify a value for the "P_Id" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Bill', 'Gates')
The above SQL statement will insert a new record into the "Persons" table. "P_Id" will be assigned a unique value. "FirstName" will be set to "Bill", and the "LastName" column will be set to "Gates".
Syntax for Access
The following SQL statement defines the "P_Id" column in the "Persons" table as an auto-increment primary key:
CREATE TABLE Persons ( P_Id int 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 each new record increments by 1.
To specify that the "P_Id" column starts at 20 and increments by 10, change autoincrement to AUTOINCREMENT(20,10):
To insert a new record into the "Persons" table, we do not need to specify a value for the "P_Id" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Bill', 'Gates')
The above SQL statement will insert a new record into the "Persons" table. "P_Id" will be assigned a unique value. "FirstName" will be set to "Bill", and the "LastName" column will be set to "Gates".
Syntax for Oracle
In Oracle, the code is a bit more complex.
You must create an auto-increment field through a sequence object (which generates a number sequence).
Please 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, starting at 1 and incrementing by 1. The 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 (P_Id, FirstName, LastName) VALUES (seq_person.nextval, 'Lars', 'Monsen')
The above SQL statement will insert a new record into the "Persons" table. The assignment of "P_Id" is the next number from the seq_person sequence. "FirstName" will be set to "Bill", and the "LastName" column will be set to "Gates".
