D. Features Introduced in Ingres 9.3 : DBMS Server Enhancements : Identity Columns
 
Share this page                  
Identity Columns
An identity column is an integer 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.
Identity columns are ideal for generating unique primary key values. Applications can use identity columns to avoid concurrency and performance problems that can result when an application generates its own counter outside of the database.
For example, if an application maintains a one-row table containing a counter, only one transaction at a time can increment the counter. In contrast, a counter maintained through an identity column achieves higher levels of concurrency because the counter is not locked by transactions. An uncommitted transaction that has incremented the counter does not prevent subsequent transactions from incrementing the counter.
Identity columns are defined with the keywords GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY in the column specifications of CREATE TABLE statements.
The identity column feature affects the syntax of the following SQL statements:
CREATE TABLE
ALTER TABLE…ALTER COLUMN
INSERT
For details, see these statement descriptions in the SQL Reference Guide.