On 2017-12-19 16:34, Nico Speleers nico.spele...@carfac.com [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
