Uniqueness Checks
INSERT OR UPDATE determines if a row exists by matching UNIQUE column values to the existing data values. If a UNIQUE constraint violation occurs, INSERT OR UPDATE performs an update operation. The UNIQUE column value can be explicitly specified in INSERT OR UPDATE or it can be the result of a column default value or a computed value.
When INSERT OR UPDATE is issued against a table that is a subclass and the super class already has the UNIQUE constraint filled, the command fails with an SQLCODE -119. However, when INSERT OR UPDATE is issued against a table that is a super class and the subclass already has the UNIQUE constraint filled, the update succeeds and fields present in both the subclass and the super class will be updated, but fields only found in the subclass will not be updated.
When INSERT OR UPDATE is run against a sharded table, if the shard key is the same as or a subset of the UNIQUE KEY constraint, INSERT OR UPDATE performs an update operation.
If the INSERT OR UPDATE attempts to perform an update because of any other unique values found (that are not the shard key), the command fails with an SQLCODE -120 error due to the unique constraint failure.
IDKEY Column Values
When using INSERT OR UPDATE, you can only insert IDKEY column values, not update them. If the table has an IDKEY index and another UNIQUE constraint, INSERT OR UPDATE matches these columns to determine whether to perform an insert or an update. If the other key constraint fails, this forces INSERT OR UPDATE to perform an update rather than an insert. However, if the specified IDKEY column values do not match the existing IDKEY column values, this update fails and generates an SQLCODE -107 error, because the update is attempting to modify the IDKEY columns.
Consider a table with columns A, B, C, and D. The table has a primary key of (A,B) in an environment where the primary key is the IDKEY, and a UNIQUE constraint on columns (C,D).
SET OPTION PKEY_IS_IDKEY = TRUE
CREATE TABLE ABCD (
A INTEGER,
B INTEGER,
C INTEGER,
D INTEGER,
CONSTRAINT AB PRIMARY KEY (A,B),
CONSTRAINT CD UNIQUE (C,D))
The table also has two rows of data:
INSERT INTO ABCD SET A=1, B=1, C=2, D=2
INSERT INTO ABCD SET A=1, B=2, C=3, D=4
Suppose you try to insert this value:
INSERT OR UPDATE ABCD (A,B,C,D) VALUES (2,2,3,4)
Because the UNIQUE (C,D) constraint failed, this statement cannot perform an insert. Instead, it attempts to update Row 2. The IDKEY for Row 2 is (1,2), so the INSERT OR UPDATE statement attempts to change the column A value from 1 to 2. Because you cannot change an IDKEY value, the update fails with an SQLCODE -107 error.
Reset your environment to the default settings, where the primary key is not the IDKEY.
SET OPTION PKEY_IS_IDKEY = FALSE