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
You want col1 to behave like an autoincrement/identity field.
- CREATE TABLE new_one
- col1 integer NOT NULL,
- col2 CHAR(10),
- PRIMARY KEY (col1)
You create a generator
- CREATE GENERATOR my_gen_id;
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:
And as shammat pointed out here "Basically the same technique as with Oracle's sequences".
- CREATE TRIGGER autoincrementor_id FOR new_one
- ACTIVE BEFORE INSERT POSITION 0
- IF (NEW.col1 IS NULL) THEN
- NEW.col1 = GEN_ID(my_gen_id,1);
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;
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.
Firebird 2 introduced a new (and better) synthax fully documented in the Firebird Generator Guide.
Firebird 3.0, thanks to Adriano dos Santos Fernandes will support IDENTITY columns, so no need to use this trick anymore.