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