SQL AUTO INCREMENT field
Auto-increment in the new record will generate a unique number into the table when.
AUTO INCREMENT field
We usually want to insert a new record each time, automatically create the value of the primary key field.
We can create an auto-increment field in the table.
The syntax for MySQL
The following SQL statement to "Persons" table in the "ID" column is defined as 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 an auto-increment.
By default, the starting value AUTO_INCREMENT is 1, and is incremented by 1 for each new record.
Let the AUTO_INCREMENT sequence start with another value, use the following SQL syntax:
To insert a new record in the "Persons" table, we do not have to "ID" column predetermined value (automatically adds a unique value):
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record in the "Persons" table. "ID" column is given a unique value. "FirstName" column would be set to "Lars", "LastName" column would be set to "Monsen".
The syntax for SQL Server
The following SQL statement to "Persons" table in the "ID" column is defined as 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 using the IDENTITY keyword to perform an auto-increment.
In the example above, the start value IDENTITY is 1, and is incremented by 1 for each new record.
Tip: To specify "ID" column to the starting 10 and 5 increments, please identity to IDENTITY (10,5).
To insert a new record in the "Persons" table, we do not have to "ID" column predetermined value (automatically adds a unique value):
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record in the "Persons" table. "ID" column is given a unique value. "FirstName" column would be set to "Lars", "LastName" column would be set to "Monsen".
The syntax for Access
The following SQL statement to "Persons" table in the "ID" column is defined as 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 using AUTOINCREMENT keyword to perform an auto-increment.
By default, the starting value AUTOINCREMENT is 1, and is incremented by 1 for each new record.
Tip: To specify "ID" column to the starting 10 and 5 increments, please autoincrement to AUTOINCREMENT (10,5).
To insert a new record in the "Persons" table, we do not have to "ID" column predetermined value (automatically adds a unique value):
VALUES ('Lars','Monsen')
The SQL statement above would insert a new record in the "Persons" table. "ID" column is given a unique value. "FirstName" column would be set to "Lars", "LastName" column would be set to "Monsen".
Syntax for Oracle
In Oracle, the code is a little more complicated.
You must create auto-increment fields by 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 called seq_person, which starting with 1 and incremented by 1. The object cache 10 values to improve performance. cache option provides faster access to the number of sequence values to be stored.
To insert a new record in the "Persons" table, we must use the nextval function (this function retrieves the next value from seq_person sequence):
VALUES (seq_person.nextval,'Lars','Monsen')
The SQL statement above would insert a new record in the "Persons" table. "ID" column is assigned the next number in sequence from seq_person. "FirstName" column would be set to "Lars", "LastName" column would be set to "Monsen".