SQLCODE -530 — foreign key violation

What happened

An insert or update of child rows fails, often for a batch of records that share one parent key.

DSNT408I SQLCODE = -530, ERROR:  THE INSERT OR UPDATE VALUE OF
         FOREIGN KEY FKORDCUS IS INVALID
DSNT418I SQLSTATE   = 23503 SQLSTATE RETURN CODE

What it means

A referential constraint says every non-null foreign key value in the child table must exist as a key in the parent table. Your statement would break that rule, so Db2 rejected it. The SQLSTATE is 23503.

The SQLERRMC token gives the constraint name. Look it up in SYSIBM.SYSRELS (RELNAME, with TBNAME as the child and REFTBNAME as the parent) and SYSIBM.SYSFOREIGNKEYS for the columns.

Typical causes

• Child records loaded before their parent records.
• Reference data (customer, product, branch codes) missing in this environment.
• Parent rows deleted, or not yet inserted, by another job.
• Bad or padded key values that look right but do not match exactly.
• An UPDATE changing a foreign key to a non-existent value.

Symptoms

Fails for specific key values. Often many records fail with the same missing parent. Different from -803 (duplicate in a unique index) and from -532, which is about deleting a parent that still has children.

Where to look

• The program log: SQLCODE, constraint name, key values.
• The catalog tables SYSRELS and SYSFOREIGNKEYS.
• The parent table, queried for the missing key.
• The schedule, to see whether the parent load ran and completed.

How to diagnose

1. Record the constraint name and the failing key.
2. Find the parent table and columns from the catalog.
3. Query the parent for the key. Check for trailing spaces or case differences.
4. Check whether the parent load or update job ran, and in the right order.
5. Count how many input records reference missing parents.

How to fix

Load or correct the parent data first, then rerun or restart the child step. If parents are genuinely missing, the data owner decides whether to add them or reject the children. To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.

How to prevent

• Set scheduler dependencies so parent loads finish before child loads.
• Validate foreign keys against the parent before updating.
• Keep reference data synchronised across environments.
• Commit parents before children that depend on them.

Production considerations

Interview question

A child-table load fails with SQLCODE -530. How do you investigate?