Explanation on SQL AUTO INCREMENT fields

Auto-increment will generate a unique number when a new record is inserted into the table, which will be explained in this article.

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 "P_Id" column in the "Persons" table as the 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 auto-increment tasks.

By default, the starting value of AUTO_INCREMENT is 1 and increments by 1 for each new record.

To make the AUTO_INCREMENT sequence start with a different value, use the following SQL syntax:

ALTER TABLE Persons AUTO_INCREMENT=100

To create a new value in the "Persons" table To insert a new record in, we do not have 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 in the "Persons" table. "P_Id" will be assigned a unique value. The "FirstName" column 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 the 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 auto-increment tasks.

By default, the starting value of IDENTITY is 1 and is incremented by 1 for each new record.

To specify that the "P_Id" column starts with 20 and increments by 10, please change identity to IDENTITY(20,10)

To insert a new record in the "Persons" table, we don't have 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 in the "Persons" table. "P_Id" will be assigned a unique value. The "FirstName" column 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 the 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 auto-increment tasks.

By default, the starting value of AUTOINCREMENT is 1 and increments by 1 for each new record.

To specify that the "P_Id" column starts with 20 and increments by 10, please change autoincrement to AUTOINCREMENT(20,10)

To insert new records in the "Persons" table, we don't have 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 in the "Persons" table. "P_Id" will be assigned a unique value. The "FirstName" column will be set to "Bill" and the "LastName" column will be set to "Gates".

Syntax for Oracle

In Oracle, the code is a little more complicated.

You must create the auto-increment field via a sequence pair (the object that 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 with 1 and increments by 1. This 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 (this function 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 in the "Persons" table. The assignment of "P_Id" is the next number from the seq_person sequence. The "FirstName" column will be set to "Bill" and the "LastName" column will be set to "Gates".

Related recommendations:

About related operations of SQL ALTER TABLE statement

About SQL undoing indexes, tables and databases Related knowledge

#Related knowledge about SQL DEFAULT constraints

The above is the detailed content of Explanation on SQL AUTO INCREMENT fields. For more information, please follow other related articles on the PHP Chinese website!