What are the advantages and disadvantages of using Auto Increment Field in Firebird?

Question

I’ve heard of some people who use Campo auto increment can give complications in the database on account that it can make several jumps in the numbering and even get lost in the counts. I don’t know if that’s true, so I wanted to know what the perks and downside field-use auto increment in databases Firebird.

Answer 1

In Firebird >= 3 an identity field is a column associated with the internal sequence generator. The value is set automatically, provided that the column is omitted in the INSERT. That is, a lot of attention with this part.

I believe that this is the problem you refer to. If you do not omit the column in Insert, you take the risk of making a mistake.

Syntax:

create table t1 (
   id integer generated by default as identity primary key
)

Firebird 2.5 and above do not have auto-incremental fields. You need to build yourself a Quence and a Rigger.

Again, that’s also a problem that sticks to what you’re referring to. If you make a mistake in constructing these two objects, you can generate an "involuntary jump".

However creating the objects correctly is not difficult:

CREATE SEQUENCE t1_id_sequence;

set term !! ;
CREATE TRIGGER T1_AUTOINCREMENT FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.ID = next value for t1_id_sequence;
END!!
set term ; !!

You can also use a tool to create these objects. Dbeaver, Ibexpert and Flame Robbin perform this task in a standardized way.

Fonte1 Fonte2