On 2-4-2018 14:11, 'Joje' [email protected] [firebird-support] wrote: > I have issue where auto-increment of ID column is generating older ID’s > that is already used in table. > > Currently, my transaction table has around 16 million rows and *ID of > last inserted row is 16,594,027*. While the *generator ID its > 16,593,965*. As you can see the generator is generating ID’s that is > already present in table which causes *violation of PRIMARY or UNIQUE > KEY constraint "INTEG_105"*error on that table. > > I managed to fix this issue, recently I am facing it frequently on this > transaction table, majority of the tables had number of recor ds are > more than 2 million or there is connectivity issues with DB. > > Now, I am wondering is this related to Firebird DB or is there any other > issue. Which version of Firebird are you using (full version please)?
Do you have code that generates its own IDs? Do you have code that tries to manipulate the value using `GEN_ID(<seq_name>, -1)` (or another negative value), `ALTER SEQUENCE seq_name RESTART WITH new_val` or `SET GENERATOR seq_name TO new_val`? Do you allow your code to specify its own IDs instead of using the sequence? It could be a bug in Firebird, but we would really need to see a reproducible case. Mark -- Mark Rotteveel
