Technote (FAQ)
Question
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?
Cause
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.
Answer
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.
Rate this page:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.