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.

Example:


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

/* we want field ID to be autoincrement */

CREATE GENERATOR gen_t1_id;
SET GENERATOR gen_t1_id TO 0;

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 ; !!


This might seem awkward, but it's a proper way to do it in 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 INSERT statement, use the RETURNING clause (only available in Firebird 2.1 and above). If you use older version of Firebird, you should first get the value using GEN_ID and then use it in INSERT statement.

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.