WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

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 created automatically 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:

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 the AUTO_INCREMENT keyword to perform auto-increment tasks.

By default, the start value of AUTO_INCREMENT is 1, and every new record is incremented by 1.

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

ALTER TABLE Persons AUTO_INCREMENT = 100

To insert a new record into the "Persons" table, we don't have to specify a value for the "ID" column (a unique value is automatically added):

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

The above SQL statement inserts a new record into 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:

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-increment tasks.

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

Tip: To specify that the "ID" column starts with 10 and increments by 5, change the identity to IDENTITY (10,5).

To insert a new record into the "Persons" table, we don't have to specify a value for the "ID" column (a unique value is automatically added):

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

The above SQL statement inserts a new record into 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:

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-increment tasks.

By default, the start value of AUTOINCREMENT is 1, and every new record is incremented by 1.

Tip: To specify that the "ID" column starts with 10 and increments by 5, change autoincrement to AUTOINCREMENT (10,5).

To insert a new record into the "Persons" table, we don't have to specify a value for the "ID" column (a unique value is automatically added):

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

The above SQL statement inserts a new record into 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 slightly more complicated.

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

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, which starts at 1 and increments by 1. The object caches 10 values ​​to improve performance. The cache option specifies how many sequence values ​​should be stored for faster access.

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 (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')

The above SQL statement inserts a new record into the "Persons" table. The "ID" column is 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".