Creating an auto incrementing column in Firebird

or in any database that supports triggers and generators (aka sequences) ...
The answer is well known however the question comes out quite often, be it in an Oracle, Firebird or PostgreSQL forum.
Well, the PostgreSQL guys took a radical approach and embedded the common solution into a datatype named SERIAL, what follows is an example aimed at the Firebird newbie.

Say you have a table like this

CREATE TABLE new_one
(
col1 integer NOT NULL,
col2 CHAR(10),
PRIMARY KEY (col1)
); 

You want col1 to behave like an autoincrement/identity field.
You create a generator

CREATE GENERATOR my_gen_id; 

(you could set more options).
Now you want it to kick in automatically, just like an autoincrement, no need to explictly call it,
you can achieve this through a trigger:

CREATE TRIGGER autoincrementor_id FOR new_one
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.col1 IS NULL) THEN
NEW.col1 = GEN_ID(my_gen_id,1);
END 

And as shammat pointed out here "Basically the same technique as with Oracle's sequences".

To retrieve the current value after an insert (if you aren't using Firebird 2.1 RETURNING clause):

SELECT GEN_ID( MY_GEN_ID, 0 ) FROM RDB$DATABASE;

Addendum:
Sometimes people might want to empty the table and reset the "autoincrement" id (a questionable approach IMHO if that value has any kind of meaning).
This can be accomplished by a simple:

SET GENERATOR MY_GEN_ID TO 1; 

Where 1 is the chosen restart value.
NOTE that it will be the current value so your trigger will start with 2, you might want to set it to 0 instead of 1.

Addendum 2:
Firebird 2 introduced a new (and better) synthax fully documented in the Firebird Generator Guide.

Addendum 3:
Firebird 3.0, thanks to Adriano dos Santos Fernandes will support IDENTITY columns, so no need to use this trick anymore.

An Opensource approach to engineering

I love this guys!

Image extracted with GIMP and it's excellent SIOX tool