What is an auto-increment field (identity) in SQL?

Identity in SQL is a feature that creates a self-increasing sequence in a table. When an identity column is defined in a table, the value of the column is automatically incremented every time new data is inserted, and a default step size of 1 is used.

The Identity attribute is usually used to define the primary key column to ensure that each row of data has a unique identifier. It can be used when creating a table or added to an existing table by modifying column properties.

Here are some specific code examples to demonstrate how to use the identity attribute:

1. Add the identity column when creating the table:

CREATE TABLE Persons
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

In the above example , the ID column is defined as the identity column, and uses the default initial value of 1 and step size of 1. It serves as the primary key column and is used to uniquely identify each person.

2. Add an identity column to an existing table:

ALTER TABLE Persons
ADD ID INT IDENTITY(1,1) PRIMARY KEY

In the above example, we use the ALTER TABLE statement to add a new one to the existing Persons table identity column ID and use it as the primary key column.

3. When new data is inserted, the value of the identity column will automatically increment:

INSERT INTO Persons (FirstName, LastName)
VALUES ('John', 'Doe')

-- 此时ID列的值为1

INSERT INTO Persons (FirstName, LastName)
VALUES ('Jane', 'Smith')

-- 此时ID列的值为2

In the above example, we inserted two new rows of data into the Persons table. Since the ID column is an identity column, its value is automatically incremented, 1 for the first insertion and 2 for the second insertion.

It should be noted that each table can only have one identity column. If there is already an identity column in the table, but you want to add another identity column to the table, you may consider using a view to achieve a similar effect.

To summarize, identity is a feature in SQL used to create self-increasing sequences. It is useful when defining primary key columns to ensure that each piece of data has a unique identifier. The above are some specific code examples about identity, I hope they can help you understand and use it.

The above is the detailed content of What is an auto-increment field (identity) in SQL?. For more information, please follow other related articles on the PHP Chinese website!