We have a table with a single unique index. The index is implicitly created by the primary key, which is on an integer identity column. Every so often an application gets the following error during an insert

ERROR [23505] [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "MYUSER.MYTABLE" from having duplicate rows for those columns. SQLSTATE=23505

The insert statement specifies DEFAULT for the identity column.

The application is not thrashing the system and the database server is very lightly loaded. On retrying the statement it generally works fine.

if your Mytab table has structure:

(id -- identity column,
name )

use insert statement as

insert into mytab(name) values ('aaa')

Is your column GENERATED BY DEFAULT? That column type does not guarentee a unique number especially if there are gaps in the numbers...i.e deleted rows.

The column is defined to allow us to insert specific values, but in fact we never do in this table (after the initial set up). Data is never deleted from the table in question.

My point is that the same insert statement sometimes fails, then simply retrying causes it to work. My initial thought was that two threads were hiting the table at the same instant. However, there is very little activity on the table and it keeps happening.

If there were a value in the table higher than the current identity column value, when we got to that value then the insert statement would always fail at that point.

I remain bemused!

Martyn

Hi ,
If you specify GENERATED ALWAYS, this problem will not come, but the draw back would be u cant insert directly into the field with identity value. U need to do a load replace.

If u have replication, GENERATED ALWAYS will not work.

In GENERATED BY DEFAULT both inserts with values and replication will work. But my doubt is if u dont give default and do the insert, it would take the next value automatically. Please try this, remove the default and remove the identity column name.

It will start putting identity values from database.

Let me know if it solved ur problem