Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
The syntax for auto-incrementing fields differs among database systems.
Use the AUTO_INCREMENT keyword.
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
PRIMARY KEY (Personid)
);
Use the IDENTITY keyword.
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL
);
IDENTITY(1,1) means the starting value is 1, and it will increment by 1 for each new record.
For Oracle and PostgreSQL, you typically use a SEQUENCE object to achieve auto-increment functionality.
Use the AUTOINCREMENT keyword.