sqlfirebirdidentityidentifierauto-increment

Firebird - serial field as in PGSQL?

PGSQL supports "serial" field type, which is converted to:

colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL

This makes the identifier generating very easy.

In the Firebird I saw only trigger based solutions.

Is it possible to use same solution in Firebird for avoid the trigger creation for each table?

colname integer DEFAULT gen_id(generator, 1)

I don't have installed Firebird version now to check it, but maybe somebody knows the answer.

Or can I use "my function" for this? Pseudo:

create function mygenid(genname) returns integer
begin
   return gen_id(genname);
end;

create table x(
   colname integer DEFAULT mygenid(generator, 1)
Solution

  • Firebird 2.5 and earlier only support triggers for generating ids. Firebird 3.0 introduced an option to specify it in the table DDL with generated by default as identity:

    create table objects (
   id integer generated by default as identity primary key,
   name varchar(15)
);

    This is syntactic sugar that will create the trigger and associated sequence for you.

    In addition, Firebird 4.0 introduced generated always as identity, which will - by default - not allow you to insert your own values, unless you're using the overriding system value clause.