<< IDE (Integrated Development Environment) | IBExpert Glossary | In limbo transactions >>

The IDENTITY data type was introduced in Firebird 3.0.

The IDENTITY data type spawns unique identifiers for the defined column from an internal generator.

The following is an excerpt from the The Firebird 3.0 Release Notes (27 January 2014 - Document v.0300-08 - for Firebird 3.0 Alpha 2):

IDENTITY column type

Adriano dos Santos Fernandes

An identity column is a column associated with an internal sequence generator. Its value is set automatically when the column is omitted in an INSERT statement.

Syntax Patterns

 <column definition> ::=
     <name> <type> GENERATED BY DEFAULT AS IDENTITY [ (START WITH <value>) ]<constraints>

When defining a column, the optional START WITH clause allows the generator to be initialised to a value other than zero. See Tracker ticket CORE-4199.

 <alter column definition> ::=
     <name> RESTART [ WITH <value> ]

A column definition can be altered to modify the starting value of the generator. RESTART alone resets the generator to zero; the option WITH <value> clauselet allows the restarted generator to start at a value other than zero. See Tracker ticket CORE-4206.

Rules:

• The data type of an identity column must be an exact number type with zero scale. Allowed types are thus SMALLINT, INTEGER, BIGINT, NUMERIC(x,0) and DECIMAL(x,0).
• An identity column cannot have DEFAULT or COMPUTED value.

Notes:

• An identity column cannot be altered to become a regular column. The reverse is also true.
• Identity columns are implicitly NOT NULL (non-nullable).
• Uniqueness is not enforced automatically. A UNIQUE or PRIMARY KEY constraint is required to guarantee uniqueness.

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;

           ID NAME
 ============ ===============
            1 Table
            2 Book
           10 Computer

Implementation Details

Two new columns have been inserted in RDB$RELATION_FIELDS to support identity columns: 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.
• Currently, RDB$IDENTITY_TYPE always stores the value 0 (GENERATED BY DEFAULT) for identity columns and NULL for non-identity columns. In the future this column will be able to store the value 1 (GENERATED ALWAYS) when that type of identity column is supported by Firebird.

Source: The Firebird 3.0 Release Notes by Helen Borrie (Collator/Editor): 27 January 2014 - Document v.0300-08 - for Firebird 3.0 Alpha 2.

back to top of page
<< IDE (Integrated Development Environment) | IBExpert Glossary | In limbo transactions >>