Firebird SQL auto-increment

Question

I’m taking an SQL/Modeling course in mysql the creation of a composite table would look like this:

USE banco;
CREATE TABLE clientes(
   cli_id integer not null auto_increment,
   cli_nome varchar(20) not null,
   cli_email varchar(30) not null
primary key (cli_id)
); /*Cria a tabela no mysql*/

Okay, now on Firebird I have to do:

CREATE TABLE clientes(
   cli_id integer not null auto_increment,
   cli_nome varchar(20) not null,
   cli_email varchar(30) not null
primary key (cli_id)
); /*cria a tabela no firebird*/
create generator gen_cidades_id /*Cria gerador auto increment*/
SET TERM ^
   CREATE TRIGGER TR_CIDADES FOR CIDADES
   ACTIVE BEFORE INSERT POSITION 0
      AS
BEGIN
    new.CID_CODIGO =gen_id(ge_cidades_id, 1);
END ^

There is no other way to do the auto_increment in Firebird besides this ? for this part here:

create generator gen_cidades_id /*Cria gerador auto increment*/
SET TERM ^
  CREATE TRIGGER TR_CIDADES FOR CIDADES
  ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  new.CID_CODIGO =gen_id(ge_cidades_id, 1);
END ^

I didn’t understand her and it’s very complicated, in my view.

Answer 1

No, Firebird offers no other way to implement auto-increment column other than this.

Eventually you can use other Firebird features and invent another way, but the shape offered by the tool is this.

And there’s nothing wrong with that. Firebird follows the pattern SEQUENCE SQL, the same implemented by other major banks.

Explaining the Firebird code

The indentation of your code may be adding to the confusion. CREATE GENERATOR is a thing and CREATE TRIGGER is another. The second is not part of the body of the first as its indentation suggests.

I modified the comments to explain the code:

/*Cria uma sequence ou generator chamada gen_cidades_id */
create generator gen_cidades_id

/*Cria uma trigger que será disparada quando 
 um novo registro for inserido em CIDADES.
 Esta trigger solicitará um novo valor 
 para a coluna CID_CODIGO */
SET TERM ^
CREATE TRIGGER TR_CIDADES FOR CIDADES
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    /* "gen_id" é uma função do Firebird.
     É esta função quem de fato cria um novo valor para a sequence "ge_cidades_id",
     incrementado em 1 conforme informado no segundo parâmetro. */
    new.CID_CODIGO =gen_id(ge_cidades_id, 1);
END ^

See that the instruction auto_increment in the table creation is not itself the auto-increment feature of Firebird.

This instruction is dispensable for creating auto-increment columns. In your case, auto_increment is a user-defined type of your database, and is probably just redundantly determining the type of the (integer) field. If you remove the auto_increment of create table, Quence will continue to function normally.

Completion

Firebird follows the SQL standard for auto-increment columns. In this pattern, two features are used: SEQUENCE, which is capable of obtaining a numerical increment, and TRIGGER, which is a trigger triggered at each new insertion. In this trigger, the SEQUENCE feature is used to fill the auto-increment column.

Answer 2

In Firebird 3, you now have auto-increment field support:

id integer generated by default as identity primary key