-803 AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X record-id
Explanation
The table that is the object of the insert or update operation is constrained to have unique values in certain columns. Completion of the requested operation would result in duplicate values.
- indexspace-name
- The name of a hash overflow index or the name of the index space that contains the unique index that enforces a primary key.
- record-id
- The record ID of the row where the duplicate values would exist.
When indexspace-name identifies a hash overflow index, the error occurred because a table with hash organization cannot have two rows with identical hash key values.
If the index space is an XML index, the duplicate values can be caused by the values in the XML document or by the result of data type conversion and rounding.
Otherwise, a primary key, unique constraint, or unique index exists over one or more columns of the table. The operation cannot be performed because one or more values would have produced a duplicate key in the unique index or constraint.
If a view is the object of the insert or update statement, the table that defines the view is constrained. The update might also be caused by a delete operation of a parent row that cascades to a dependent row with a delete rule of SET NULL.
System action
The statement cannot be processed.
Programmer response
Examine the definitions for UNIQUE INDEX or the HASH key that is specified in the index space that is identified by indexspace-name to determine the uniqueness constraint that is imposed. Refer to SYSIBM.SYSINDEXES for the index space name and the associated index name.
- Update operation
- Verify that the specified operation is consistent with the uniqueness constraint. If this does not indicate the error, examine the object table to determine the cause of the problem.
- Insert operation
- Examine the object table to determine which values violate the uniqueness constraint. If the INSERT statement contains a subquery, match the contents of the table addressed by the subquery and the contents of the object table to determine the cause of the problem.
- Delete operation
- Examine the index key columns in the table that defines the index. These columns contain a foreign key, which when set NULL on a cascade delete from the object table, causes the duplicate values.
- Merge operation
- Verify that the specified operation is consistent with the uniqueness constraint. If this does not indicate the error, examine the object table to determine the cause of the problem. In very rare cases, the error can be caused by another INSERT/UPDATE statement which modified the object table during the processing of MERGE statement. Under such circumstances, examine your applications and make appropriate changes to handle concurrency. For example, changing the isolation level or running the MERGE statement again might avoid the problem.
SQLSTATE
23505
