On 2017-12-19 16:34, Nico Speleers nico.speleers@...
[firebird-support] wrote:

> Hi,
>
> Ex.
> CREATE TABLE ADDRESSES (
> ID INTEGER GENERATED BY DEFAULT AS IDENTITY
> NOT NULL,

[..]

> /* Keys */
> CONSTRAINT PK_ADDRESSES
> PRIMARY KEY (ID),
> /* Foreign keys */
> CONSTRAINT FK_ADDRESSES_POSTALCODEID
> FOREIGN KEY (POSTALCODEID)
> REFERENCES POSTALCODES(ID)
> );
>
> Id = 1 / AddressName = Test / ......
> Id = 2 / AddressName = Test2 / ......
>
> I want to know the next Id number in my table Addresses without an
> insert. In my example it will be 3. I want to reserve (without an
> insert) Id number 3 because someone else can also ask the next Id
> number. In that case it must be id number = 4, because you've got
> Number 1 and 2 in the table and number 3 is reserved.
>
> How can I do that ?

Why do you want to do that? This sounds a bit like an XY-problem, you
might want to start with describing the underlying problem you are
trying to solve.

The whole point of using an identity column is that it is assigned at
insert time. Trying to know it before hand defeats the purpose, and
can't be guaranteed transactionally (if a concurrent transaction would
insert a row, that transaction would get the 'next' id value, not your
transaction).

Instead, use insert ... returning id to insert a row and obtain the
generated identifier, and then do whatever needs to be done with that
identifier.

Alternatively, as suggested by Dmitry, use a sequence instead.

I hope you are also aware that sequences (and identity columns for that
matter) are atomic, but not transactional: once a value has been
'generated', even a transaction rollback will not revert the value.

So:

start transaction
insert row (generated id = 1)
rollback
start transaction
insert row (generated id = 2, and not 1!)
...

Mark