FirebirdSQL logo
 DOMAININDEX 

Domain-based Columns

To define a column, you can use a previously defined domain.If the definition of a column is based on a domain, it may contain a new default value, additional CHECK constraints, and a COLLATE clause that will override the values specified in the domain definition.The definition of such a column may contain additional column constraints (for instance, NOT NULL), if the domain does not have it.

Important

It is not possible to define a domain-based column that is nullable if the domain was defined with the NOT NULL attribute.If you want to have a domain that might be used for defining both nullable and non-nullable columns and variables, it is better practice defining the domain nullable and apply NOT NULL in the downstream column definitions and variable declarations.

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).

GENERATED BY DEFAULT

An identity column of type GENERATED BY DEFAULT will generate a value on insert if no value — other than DEFAULT — is specified on insert.When the OVERRIDING USER VALUE clause is specified in the INSERT statement, the user-provided value is ignored, and an identity value is generated (as if the column was not included in the insert, or the value DEFAULT was specified).