MainframeKit

-530

SQLSTATE 23503NEGATIVE SQLCODE

An INSERT, UPDATE or MERGE set a foreign key to a value that has no matching parent row, so the referential constraint was violated.

MOST LIKELY

Loading or inserting child rows before their parent rows.

FIRST CHECK

Take the constraint name from the message and find the parent table and columns in SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS.

TRY IT ▸

MainframeKit assistant

What it means

The table has a foreign key that points to a parent table. Every non-null foreign key value must match a primary or unique key value in the parent. Your statement tried to store a value that does not exist in the parent, so Db2 rejected it and left the table unchanged.

The message gives the constraint name, which tells you which foreign key and which parent table are involved (look it up in SYSIBM.SYSRELS). For temporal tables the business-time period must also be covered by matching parent rows.

Typical causes

  • Loading or inserting child rows before their parent rows.
  • Reference data (the parent table) missing in this environment.
  • A typo, padding or case difference in the key value.
  • An UPDATE that changes a foreign key to a value not in the parent.

How to fix it

  1. Take the constraint name from the message and find the parent table and columns in SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS.
  2. Query the parent table for the value you tried to insert.
  3. Insert the parent row first, or correct the foreign key value.
  4. Fix the order of jobs or loads so parents are always loaded before children.

Example

Illustrative only; names and values are invented.

SQLCODE = -530, SQLSTATE = 23503
THE INSERT OR UPDATE VALUE OF FOREIGN KEY FKDEPT IS INVALID