Firebird sequence-backed ID shorthand

Question

What do others do to simplify the creation of simple, serial surrogate keys populated by a SEQUENCE (a.k.a. GENERATOR) in Firebird >= 2.1? I finc the process comparatively arduous:

For example, in PostgreSQL, I simply type:

pg> CREATE TABLE tbl (
  >   id SERIAL NOT NULL PRIMARY KEY,
  > ...

In MySQL, I simply type:

my> CREATE TABLE tbl (
  >   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  > ...

But in Firebird I type:

fb> CREATE TABLE tbl (
  >   id BIGINT NOT NULL PRIMARY KEY,
  > ...

fb> CREATE SEQUENCE tbl_id_seq;

fb> SET TERM !!;
  > CREATE TRIGGER tbl_id_trg FOR tbl
  > ACTIVE BEFORE INSERT POSITION 0
  > AS
  > BEGIN
  >   IF ((new.id IS NULL) OR (new.id <= 0)) THEN
  >   BEGIN
  >     new.id = GEN_ID(tbl_id_seq, 1);
  >   END
  > END !!
  > SET TERM ; !!

... and I get pretty bored by the time I reach trigger definition. However, I routinely make SEQUENCE-backed ID fields for temporary, developement and throw-away tables. What do others do to simplify this? Work with an IDE? Run a pre-processing, in-house perl script over the DDL file? Etc.

Answer 1

Use IBExpert and you can make it in 4 clicks

I think SEQUENCE is in the SQL Standard.

Answer 2

You can always create the sequence and no trigger and use the GEN_ID(tbl_id_seq, 1) in your insert query. But as Hugues already said: there are tools (like IBExpert) that will make it very simple for you to generate the boilerplate code.

Answer 3

You have to understand that a generator/sequence is something different from an "auto increment" attribute. Sequences can be used to generate numbers, period. These numbers can be used to do all sorts of things, for example, assign them to a column value. This can be done from a Trigger, but also from the client.

You can get a new value by doing:

select gen_id(mygenerator, 1) as new_value from rdb$database

Next, use this value in an INSERT statement. The good thing about having the value "up front" is, that you can also use this as a foreign key value when inserting child records.

Many tools, including Database Workbench, have functionality that allows you to easily create such "auto increment triggers" visually.