Easiest way to create an auto increment field in Firebird database

Question

Is there an easy way to create auto increment field using Firebird? I have installed the FlameRobin admin tool, but the process of creating an auto increment field through the tool is complex. Can I not create such an identity field just by clicking a checkbox or using some other tool other than Flamerobin?

Answer 1

Firebird 3 and later

In Firebird 3 it is easy to create, as it introduced identity columns. In practice it is syntactic sugar for generating a sequence + trigger (as shown for Firebird 2.5) for you.

For example

create table t1 (
   id integer generated by default as identity primary key
)

Firebird 3 only supports "generated by default", which means users are able to specify their own id values (which might lead to duplicate value errors); "generated always" has been added in Firebird 4.

See also the Firebird 3 release notes, section "Identity Column Type", and the Firebird 4.0 Language Reference, section "Identity Columns (Autoincrement)".

Firebird 2.5 and earlier

Firebird 2.5 and earlier do not have auto-increment fields. You need to create them yourself with a sequence (aka generator) and a trigger.

Sequence is the SQL standard term and generator is the historical Firebird term; both terms are available in the Firebird DDL syntax.

To create a sequence:

CREATE SEQUENCE t1_id_sequence;

To create a trigger to always generate the id on a table T1 with primary key ID:

set term !! ;
CREATE TRIGGER T1_AUTOINCREMENT FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.ID = next value for t1_id_sequence;
END!!
set term ; !!

See also: How to create an autoincrement column?

Using FlameRobin

FlameRobin also provides tooling to create a sequence + trigger for you. If you have an existing table, you can follow these steps:

1. Open the table properties:

   

2. Open the column properties of the primary key column

   

3. Default column properties, select new generator and create trigger:

   

4. Generator (sequence) and trigger code generated by FlameRobin. Note that contrary to my example above this trigger allows a user to specify their own id value, with some logic to avoid future duplicates. Execute this (and don't forget to commit):