This ticket deals with IDENTITY columns and if they are guaranteed unique.
Even though your use case scenario may not apply here, the uniqueness is NOT guaranteed as you can UPDATE the IDENTITY column. The way around this is to make the IDENTITY column a primary key. Probably not doable for most production database repositories.
specifically, the part, "Identity columns cannot be considered as a constraint, that is, identity columns do not guarantee unique values. However, the values are unique as long as they areand are not manually changed." This means a series of concurrent inserts where the IDENTITY is not included in the values inserted, then the FIFO rule applies, where the first insert does a commit and generates the IDENTITY, then the next insert is free to insert. The emphasis is on theinsert implicitly- where the IDENTITY value is not specified, but system generated. If you do an insert with the IDENTITY value coded in the insert statement if will insert and become a duplicate. Remember, the IDENTITY column can't be considered a constraint.
There are a couple of suggestions to get closer to guaranteed uniqueness: 1. LOCK the table you are inserting into - which means no one can insert until you either commit or rollback. This can be achieved with: DELETE FROM <tableName> where FALSE; 2. Do the insert 3. Release lock with commit or rollback.
Another suggestion is to create a function to insert the max(ROW_NUMBER()) + 1 into the IDENTITY column. It's at the bottom of this simple example SQL.
drop schema exa_29444 CASCADE;
create schema exa_29444;
open schema exa_29444;
– -- Build a test table with an IDENTITY column. –
create or replace table identity_test (id int identity, name varchar(20));