FirebirdSQL logo
 DOMAININDEX 

Identity Columns (Autoincrement)

Identity columns are defined using the GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY clause.The identity column is a column associated with an internal sequence.Its value is set automatically every time it is not specified in the INSERT statement, or when the column value is specified as DEFAULT.

Rules

  • The data type of an identity column must be an exact number type with zero scale.Allowed types are SMALLINT, INTEGER, BIGINT, NUMERIC(p[,0]) and DECIMAL(p[,0]) with p <= 18.

    • The INT128 type and numeric types with a precision higher than 18 are not supported.

  • An identity column cannot have a DEFAULT or COMPUTED value.

  • An identity column can be altered to become a regular column.

  • A regular column cannot be altered to become an identity column.

  • Identity columns are implicitly NOT NULL (non-nullable), and cannot be made nullable.

  • Uniqueness is not enforced automatically.A UNIQUE or PRIMARY KEY constraint is required to guarantee uniqueness.

  • The use of other methods of generating key values for identity columns, e.g. by trigger-generator code or by allowing users to change or add them, is discouraged to avoid unexpected key violations.

  • The INCREMENT value cannot be zero (0).

GENERATED ALWAYS

An identity column of type GENERATED ALWAYS will always generate a column value on insert.Explicitly inserting a value into a column of this type is not allowed, unless:

  1. the specified value is DEFAULT;this generates the identity value as normal.

  2. the OVERRIDING SYSTEM VALUE clause is specified in the INSERT statement;this allows a user value to be inserted;

  3. the OVERRIDING USER VALUE clause is specified in the INSERT statement;this allows a user specified value to be ignored (though in general it makes more sense to not include the column in the INSERT).