SQLCODE -803 error: An inserted or updated value is invalid when the IDENTITY column in the row is defined GENERATED BY DEFAULT
An IDENTITY column in the row is defined as GENERATED BY DEFAULT, and I have defined a unique index on the column to enforce uniqueness. Previously, I inserted some rows with specific key values that I generated. Now, when I let DB2® generate default values, I get an SQLCODE -803 error for duplicate values:
DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID
BECAUSE INDEX IN INDEX SPACE XPERSON CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.
Why doesn't DB2 generate a unique value?
DB2 generates a value for an identity column without verifying its uniqueness. The unique index enforces uniqueness. You cannot make DB2 generate an alternate value that is guaranteed to be unique.
In Version 7 the recommended circumvention is:
1. Unload the data from the table.
2. Drop and recreate the table with the IDENTITY column that has a START WITH value that is higher than the current maximum value. (Also recreate any indexes on the table, including the unique index on the IDENTITY column.)
3. Reload the data into table.
In Version 8 this can be solved using ALTER TABLE to set a RESTART WITH value for the IDENTITY column.