DB2 SQLCODEs: RI and Constraints (-530 to -545, -803, -811)

Quick map of the family

Treat this group as one mental model: parents and children (-530, -531, -532), row rules (-545), uniqueness (-803), and singleton cardinality (-811). All leave the target table unchanged for the failing statement. DSNTIAR / GET DIAGNOSTICS MESSAGE_TEXT shows the IBM text with tokens filled in.

-530 Foreign key value is invalid

IBM: THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint-name IS INVALID. An INSERT or UPDATE tried to put a value in a foreign key that does not equal any parent key in the parent table of that relationship. SQLSTATE 23503. System action: the statement cannot run; the object table is unchanged.

Programmer response: compare the insert/update FK value to every parent key. Common causes include inserting the child before the parent, a typo in the key, wrong qualifier, or loading child rows from a file that was never reconciled. For temporal referential constraints, BUSINESS_TIME on the child must sit inside contiguous matching parent BUSINESS_TIME periods—not only the key columns.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Parent
CREATE TABLE DEPT (
  DEPTNO CHAR(3) NOT NULL PRIMARY KEY,
  DEPTNAME VARCHAR(36) NOT NULL
);
 
-- Child
CREATE TABLE EMP (
  EMPNO CHAR(6) NOT NULL PRIMARY KEY,
  WORKDEPT CHAR(3),
  CONSTRAINT FK_DEPT FOREIGN KEY (WORKDEPT)
    REFERENCES DEPT (DEPTNO)
);
 
-- -530: no DEPT row for 'Z99'
INSERT INTO EMP (EMPNO, WORKDEPT) VALUES ('000010', 'Z99');
 
-- OK once parent exists
INSERT INTO DEPT VALUES ('A00', 'SPIFFY COMPUTER SERVICE DIV.');
INSERT INTO EMP (EMPNO, WORKDEPT) VALUES ('000010', 'A00');

-531 Parent key cannot be updated

IBM: PARENT KEY IN A PARENT ROW CANNOT BE UPDATED BECAUSE IT HAS ONE OR MORE DEPENDENT ROWS IN RELATIONSHIP constraint-name. SQLSTATE 23504. For modern binds and dynamic SQL, this often appears when a multi-row update would remove a parent key value that dependents still use. Older binds blocked updating a primary key that had any dependents.

Do not “force” the parent key change. Update dependent foreign keys to the new parent value (or a different existing parent), delete dependents if business rules allow, then update the parent—or introduce a surrogate key so business attributes are not the RI key. The UPDATE does not change the table when -531 is returned.

1
2
3
4
5
6
7
-- EMP.WORKDEPT still points at 'A00'
UPDATE DEPT SET DEPTNO = 'A01' WHERE DEPTNO = 'A00';  -- -531
 
-- Safer pattern: re-point children, then rename parent
UPDATE EMP SET WORKDEPT = 'A01' WHERE WORKDEPT = 'A00';
INSERT INTO DEPT VALUES ('A01', 'SPIFFY COMPUTER SERVICE DIV.');
DELETE FROM DEPT WHERE DEPTNO = 'A00';

-532 Delete restricted by relationship

IBM: THE RELATIONSHIP constraint-name RESTRICTS THE DELETION OF ROW WITH RID X rid-number. A DELETE tried to remove a parent (or cascaded to a row) that still has a dependent under RESTRICT or NO ACTION. SQLSTATE 23504. Contents of the object table stay unchanged.

• RESTRICT / NO ACTION — parent delete fails while children exist
• CASCADE — children delete with the parent (watch for longer cascade chains that later hit RESTRICT)
• SET NULL / SET DEFAULT — children stay, FK nulled or defaulted (can collide with NOT NULL or CHECK—see related codes such as -543)

Programmer response: use constraint-name and RID X rid-number to find the blocking child. Delete or reassign dependents, change the delete rule with care, or stop deleting the parent. ALTER TABLE … ALTER PART ROTATE FIRST TO LAST can also raise -532 when DELETE RESTRICT prevents clearing partition data that is still referenced.

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE EMP
  ADD CONSTRAINT FK_DEPT FOREIGN KEY (WORKDEPT)
  REFERENCES DEPT (DEPTNO)
  ON DELETE RESTRICT;
 
-- -532 while EMP still references A00
DELETE FROM DEPT WHERE DEPTNO = 'A00';
 
-- Delete or reassign children first
UPDATE EMP SET WORKDEPT = NULL WHERE WORKDEPT = 'A00';
DELETE FROM DEPT WHERE DEPTNO = 'A00';

-545 Check constraint violated

IBM: THE REQUESTED OPERATION IS NOT ALLOWED BECAUSE A ROW DOES NOT SATISFY THE CHECK CONSTRAINT check-constraint. INSERT, UPDATE, or MERGE would leave a row that fails the table’s CHECK. Typical SQLSTATE is 23513. System action: statement fails; table contents unchanged.

Programmer response: query SYSIBM.SYSCHECKS for the named constraint and compare the predicate to the values you sent. Fix the application data, or ALTER the CHECK if the business rule really changed. Do not confuse -545 with -803 (uniqueness) or -530 (FK)— CHECK is a boolean expression on the row, not a parent lookup.

1
2
3
4
5
6
7
8
ALTER TABLE EMP
  ADD CONSTRAINT CK_SALARY CHECK (SALARY >= 0);
 
-- -545
UPDATE EMP SET SALARY = -100 WHERE EMPNO = '000010';
 
-- OK
UPDATE EMP SET SALARY = 50000 WHERE EMPNO = '000010';

-803 Duplicate key (unique / primary / hash)

IBM: 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. SQLSTATE 23505. This is the everyday “duplicate primary key” or “unique index hit” in batch loads and online inserts.

Tokens matter. indexspace-name points at the unique index (or hash overflow index). record-id is the RID of the existing row that already owns the key. For XML unique indexes, duplicates can come from XML content or from conversion/rounding. On CASCADE SET NULL from a delete, -803 can appear if nulling the FK columns would collide with another unique key—read the programmer response for DELETE carefully.

1
2
3
4
5
6
7
8
       EXEC SQL
            INSERT INTO EMP (EMPNO, LASTNAME)
            VALUES (:WS-EMPNO, :WS-LASTNAME)
       END-EXEC.
       IF SQLCODE = -803
           DISPLAY 'DUP KEY INDEXSPACE / RID IN MESSAGE'
           CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LEN
       END-IF.

1
2
3
4
5
6
7
-- Find the index behind the index space token
SELECT NAME, CREATOR, UNIQUERULE, TBNAME
FROM SYSIBM.SYSINDEXES
WHERE INDEXSPACE = 'your-indexspace-token';
 
-- Inspect the existing row (RID from message, use your shop's RID tools)
SELECT EMPNO, LASTNAME FROM EMP WHERE EMPNO = :dup-key-value;

-811 More than one row for singleton / scalar context

IBM: THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE. SQLSTATE 21000. The statement cannot be processed.

Classic case: SELECT … INTO host variables when WHERE is not unique. Contrast with +100 (zero rows) and 0 (exactly one). Also appears for UPDATE SET col = (SELECT …) when the subquery returns multiple rows, and for basic predicates like WHERE col = (SELECT …) with more than one value. Fix the predicate, add aggregation, or switch to a cursor / multi-row FETCH.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- -811 if two employees share LASTNAME = 'SMITH'
SELECT EMPNO, SALARY
  INTO :H-EMPNO, :H-SALARY
  FROM EMP
 WHERE LASTNAME = 'SMITH';
 
-- Prefer unique key
SELECT EMPNO, SALARY
  INTO :H-EMPNO, :H-SALARY
  FROM EMP
 WHERE EMPNO = '000010';
 
-- Or use a cursor when many rows are valid
DECLARE C1 CURSOR FOR
  SELECT EMPNO, SALARY FROM EMP WHERE LASTNAME = 'SMITH';

-803 versus -530 versus -545

• -803 — another row already owns this unique key
• -530 — parent table has no matching key for this FK
• -545 — this row fails a CHECK expression (parent may exist; key may be unique)

System action shared by this family

None of these codes commit a partial bad row. The failing INSERT, UPDATE, DELETE, or MERGE leaves the object table as it was for that statement. Your unit of recovery may still hold prior successful changes—decide COMMIT versus ROLLBACK from application rules. Log SQLCODE, SQLSTATE, SQLERRM tokens, and for -803/-532 the RID / index space so support can find the colliding row quickly.

Explain It Like I'm Five

Imagine cubbies with rules. -530 is putting a kid’s coat on a hook that is not on the classroom list. -531 is renaming the classroom while kids still hang coats on the old name. -532 is throwing away the classroom hook board while coats are still hanging. -545 is a coat that fails the “must be dry” rule painted on the wall. -803 is two coats fighting for the same numbered hook. -811 is asking “hand me the one red coat” when three red coats exist.

Exercises

1. Build parent/child tables with ON DELETE RESTRICT. Produce -530, -532, and -531 in order; write the SQLSTATE for each.
2. Force -803 twice: once with a primary key, once with a separate UNIQUE index. Compare the indexspace-name tokens.
3. Write a SELECT INTO that returns -811, then rewrite it so it returns +100, then so it returns 0.
4. Add a CHECK (SALARY BETWEEN 0 AND 500000). Produce -545 and look up the constraint in SYSIBM.SYSCHECKS.
5. From a -803 DSNTIAR message, list the three tokens you would paste into a support ticket.