AUTOINCREMENT options

[Lester Caine]

Over the years there have been various additions to firebird for handling incremental values. I extended ADOdb to automatically create a generator and trigger for each AUTOINCREMENT field in the schema and that still works fine but is looking a little tricky to add to laravel so I'm looking at the other options. Since I've already moved on to Firebird5 the GENERATED ALWAYS AS IDENTITY PRIMARY KEY has been used, but I'm no quite sure just how it actually works in practice?
Is there something in between, or am I better reverting to the old methods ;)

[Dimitry Sibiryakov]

Lester Caine wrote 05.02.2026 12:31:
> Since I've already moved on to Firebird5 the GENERATED ALWAYS AS IDENTITY
> PRIMARY KEY has been used, but I'm no quite sure just how it actually works in
> practic

The same way, just the generator and triggers are created by Firebird
automagically.

--
WBR, SD.

[Lester Caine]

That is what I think I was expecting, and Flamerobin probably knows nothing about it although the DDL is looking correct. Just what generator it is using is perhaps the question but I can have a scout in the systems tables and see if there is anything there.
Looks like I need to sort lastInsertId() in the pdo driver so it does not crash things :)

[Mark Rotteveel]

On 05/02/2026 12:57, Lester Caine wrote:
> That is what I think I was expecting, and Flamerobin probably knows
> nothing about it although the DDL is looking correct. Just what
> generator it is using is perhaps the question but I can have a scout in
> the systems tables and see if there is anything there.
> Looks like I need to sort lastInsertId() in the pdo driver so it does
> not crash things :)

The generator backing the GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
column is an implementation details you should _not_ rely on. You need
to use `INSERT ... RETURNING { * | <column-list>}` to obtain generated
values.

And even in manually written triggers to generate an id, using the
generator for lastInsertId() is the wrong way as the value you obtain
that way might be from a different transaction. There you should also
use `INSERT ... RETURNING { * | <column-list>}`.

Mark
--
Mark Rotteveel

[Lester Caine]

Changing third party applications that are built on other database styles of working is always going to be a problem with abstraction layers. The laravel schema build is running nicely and I do have GENERATED working on the autoincrement fields. On investigation, part of the problem is leaving that column out of the insert query and the PDO lastInsertId() never was a good solution, I was looking at RETURNING if webtrees actually needs the generated id but it's the way it builds the queries which I'm still struggling with although the Postgres version of InsertGetId should be working. I've not actually hit it with the debugger which is why I can't see yet just what is going one :(
If I select 'BY DEFAULT' I presume I can leave the autoinc field and just ensure that the insert value is say '0' in this case? 

[Dimitry Sibiryakov]

Lester Caine wrote 05.02.2026 20:29:
> If I select 'BY DEFAULT' I presume I can leave the autoinc field and just ensure
> that the insert value is say '0' in this case?

No, this would do exactly contrary: autoinc trigger won't be fired and you
just insert record with value 0.

--
WBR, SD.

[Mark Rotteveel]

No, you can't. Doing that would insert a row with the id column having
value `0`. With BY DEFAULT, the ID column must be left out, or you need
to specify DEFAULT instead of a value, or you need to use the OVERRIDING
USER VALUE clause in your INSERT statement.

That is, assuming a table TBL_WITH_AUTOINC (ID, VALUE_COLUMN):

insert into TBL_WITH_AUTOINC (VALUE_COLUMN) values ('something');

or

insert into TBL_WITH_AUTOINC (ID, VALUE_COLUMN) values (default,
'something');

or

insert into TBL_WITH_AUTOINC (ID, VALUE_COLUMN) overriding user value
values (0, 'something');

See also
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-dml-insert-overriding

Mark
--
Mark Rotteveel

[Lester Caine]

Thanks for that Mark ... I've a better understanding of how the new process hangs together and can see that if I was starting from scratch and just looking at Firebird it's a natural progression.
While it looked like a 'quick fix' for the schema building process I think I do need to roll back to a more traditional approach which is more cross database compatible as I do need the 'sequence' name even if that is just the autoinc column name.
I am a lot further forward than I thought I would be, manually added data via Flamerobin is being read fine, so if I can JUST figure out why queries using an autoinc are currently crashing I should be there. It's just a question of at what point does one give up and resort to an alternative database engine ;) But Firebird is just so stable and easy to back up it's been my base for over 30 years now (starting with Interbase of cause)