IBM Support

SQLCODE -803 error: An inserted or updated value is invalid when the IDENTITY column in the row is defined GENERATED BY DEFAULT

Question & Answer


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.

[{"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"RDS","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"7.0;8.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21114865