---------------- Identity Columns ---------------- Author: Adriano dos Santos Fernandes <adrianosf@gmail.com> Description: An identity column is a column associated with an internal sequence generator and has it value automatically set when omitted in an INSERT statement. Syntax: <column definition> ::= <name> <type> GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <identity column option>... ) ] <constraints> <identity column option> ::= START WITH <value> | INCREMENT [ BY ] <value> <alter column definition> ::= <name> <set identity column generation clause> [ <alter identity column option>... ] | <name> <alter identity column option>... | <name> DROP IDENTITY <set identity column generation clause> ::= SET GENERATED { ALWAYS | BY DEFAULT } <alter identity column option> ::= RESTART [ WITH <value> ] | SET INCREMENT [ BY ] <value> Syntax rules: - The type of an identity column must be an exact number type with zero scale. That includes: smallint, integer, bigint, numeric(x, 0) and decimal(x, 0). - Identity columns can't have DEFAULT or COMPUTED value. Notes: - You cannot alter a identity column to normal column and vice versa. - Identity columns are implicitly NOT NULL. - Identity columns don't enforce uniqueness automatically. Use UNIQUE or PRIMARY key for that. - Increment value cannot be 0. Implementation: Two columns have been inserted in RDB$RELATION_FIELDS: RDB$GENERATOR_NAME and RDB$IDENTITY_TYPE. RDB$GENERATOR_NAME stores the automatically created generator for the column. In RDB$GENERATORS, the value of RDB$SYSTEM_FLAG of that generator will be 6. RDB$IDENTITY_TYPE stores the value 0 for GENERATED ALWAYS, 1 for GENERATED BY DEFAULT, and NULL for non-identity columns. Example: create table objects ( id integer generated by default as identity primary key, name varchar(15) ); insert into objects (name) values ('Table'); insert into objects (name) values ('Book'); insert into objects (id, name) values (10, 'Computer'); select * from objects order by id; commit; ID NAME ============ =============== 1 Table 2 Book 10 Computer alter table objects alter id restart with 14; insert into objects (name) values ('Pencil'); select * from objects order by id; ID NAME ============ =============== 1 Table 2 Book 10 Computer 15 Pencil alter table objects alter id set increment by 2; alter table objects alter id drop identity; --------------- Override Clause --------------- BY DEFAULT identity columns can be overriden in INSERT statements (INSERT, UPDATE OR INSERT, MERGE ... WHEN NOT MATCHED) just specifying the value in the values list. However, for ALWAYS identity columns that is not allowed. To use the value passed in the INSERT statement for an ALWAYS column, you should pass OVERRIDING SYSTEM VALUE as following: insert into objects (id, name) overriding system value values (11, 'Laptop'); OVERRIDING also supports a subclause to ignore the value passed in INSERT and use the defined sequence: insert into objects (id, name) overriding user value values (12, 'Laptop'); -- 12 is not used