Firebird SQL auto-increment

Question

Firebird SQL auto-increment

Navigation

#1 by (5 votes)
#2 by (1 votes)

4

I'm doing a 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*/

OK, now in 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 ^

Is there no other way to do auto_increment, in firebird beyond that? because 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 do not understand it and it's very complicated, as I see it.

sql firebird

asked by anonymous 21.11.2014 / 16:54

2 answers

1

In Firebird 3, it now supports auto-increment field:

id integer generated by default as identity primary key

17.11.2016 / 20:26

What is the equivalent function of window.location.href of JavaScript in PHP? [duplicate] Create Static SelectList and pass a selected item to View

score 5 · Accepted Answer

No, Firebird does not offer another way to implement auto-increment column beyond this one.

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

And there's nothing wrong with that. Firebird follows the SEQUENCE standard of SQL, the same one implemented by other major banks.

Explaining the Firebird code

The indentation of your code may be increasing confusion. CREATE GENERATOR is one thing and CREATE TRIGGER is another. The second is not part of the body of the first as your 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 auto_increment statement in table creation is not in itself the Firebird auto-increment feature.

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 only determining in a redundant manner the type of the (integer) field. If you remove auto_increment from create table , the sequence will continue to function normally.

Conclusion

Firebird follows the SQL standard for auto-increment columns. In this pattern, two features are used: SEQUENCE , which is able to get a numerical increment, and TRIGGER , which is a trigger triggered with each new insert. In this trigger, the SEQUENCE feature is used to populate the auto-increment column.