Identity Columns
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence.
An identity column provides a way to automatically generate a unique numeric value for each row in a table. A table can have only one column that is defined with the identity attribute.
An identity column is defined by including one of the following qualifiers on the column specification:
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
Indicates that the column value is determined by the corresponding sequence. The user cannot specify an explicit value for the column in an INSERT or UPDATE statement.
INSERT statements that contain ALWAYS identity columns in their column list must specify DEFAULT as the corresponding value. To override this behavior, use the OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE clauses of the INSERT statement.
The optional seq_name defines the name of the sequence.
The optional
seq_options define how the sequence supplies data when requested by an application. All sequence options available for the
CREATE SEQUENCE statement (see page
Syntax) are valid, except the data type option. The data type of the sequence matches the data type of the identity column.
For example:
GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 20)
GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates that the user can optionally provide an explicit value for the column.
The optional seq_name defines the name of the sequence.
The optional
seq_options define how the sequence supplies data when requested by an application. All sequence options available for the
CREATE SEQUENCE statement (see page
Syntax) are valid, except the data type option. The data type of the sequence matches the data type of the identity column.
The sequence created to manage identity column values is accessible by its generated name and can be used as any other sequence, with CURRENT VALUE and NEXT VALUE operators. The generated sequence, however, cannot be explicitly dropped; instead, the identity column or table must be dropped, or the ALTER TABLE … ALTER COLUMN … DROP IDENTITY statement must be used.