How to create an autoincrement column?

Firebird does support autoincrement columns via BEFORE INSERT TRIGGERs and GENERATORs. Generators are also named SEQUENCES in Firebird 2.0 and above - and are compliant to the SQL standard.

For example, let's suppose you have a table named T1, with the following columns:


create table t1
(
id integer not null,
field1 varchar(20) not null
);

To make column ID autoincrement, we don't have to do anything special to the column itself, but we need to create a BEFORE INSERT trigger that will put a new value each time a new record is inserted. To make sure we get unique values consistently we use a GENERATOR (a.k.a. SEQUENCE):

CREATE GENERATOR gen_t1_id;
SET GENERATOR gen_t1_id TO 0;

SET GENERATOR statement is used to set the starting value of a sequence. Since we set it to zero, the first record will get a value of one.

Now we create a trigger:

set term !! ;
CREATE TRIGGER T1_BI FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
END!!
set term ; !!

Checking whether NEW.ID is NULL is a common way to prevent getting incremented values when you copy data from some other database or import from some other data source. It can be done in many different ways, but this is the most simple approach: if value for column is not supplied - autoincrement it.

If you wonder why are those SET TERM statements needed: it's because many tools use semi-colon to separate statements and trigger code contains a semi-colon so statement terminator character needs to be changed. Please refer to FAQ #78 for more details.

All this might seem awkward, but it's a proper way to do it in a multiuser environment. Most GUI administration tools have options to generate this code for you automatically, so it is not a problem.

If you wish to get the new ID from an INSERT statement, use the RETURNING clause (only available in Firebird 2.1 and above). Here's an example that would return the newly inserted ID from an insert statement:

INSERT INTO t1(field1) VALUES('my stuff') RETURNING id;

If you execute such statement in your admin tool, you would get a single-row resultset with column ID containing newly inserted record ID.

If you use an older version of Firebird, you should first get the value using GEN_ID and then use it in INSERT statement. To read in the generator value, you can use some single-record table like RDB$DATABASE:

select GEN_ID(GEN_T1_ID, 1) from RDB$DATABASE;

If you are using Delphi and Zeos (or a similar library), you can use ZSequence component to have GEN_ID read automatically for you. In ZTable, mark the autoincrement field as SequenceField and set the appropriate ZSequence object and it will all work automatically.

If you are porting an existing MySQL application to Firebird, you might need to get the LAST_INSERT_ID. This is the last insert ID by connection. To mimic MySQL behavior in Firebird, you can use context variables. After you get the ID in before-insert triggers, store it into context variable with scope of connection:

create trigger
...
new.autoinc = gen_id(some_generator, 1);
RDB$SET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID', new.autoinc);
...

At later stage you can get the LAST_INSERT_ID using EXECUTE BLOCK if you run Firebird 2.1 or above. For earlier Firebird versions and even for 2.1 if you like some elegance, you can create a stored procedure:

CREATE PROCEDURE LAST_INSERT_ID RETURNS (ID BIGINT) AS
BEGIN
id = RDB$GET_CONTEXT('USER_SESSION', 'LAST_INSERT_ID');
suspend;
END

In your code just replace MySQL calls with:

select ID from LAST_INSERT_ID;