MainframeKit

-532

SQLSTATE 23504NEGATIVE SQLCODE

Db2 refused to delete a parent row because rows in another table still point at it and the foreign key's delete rule does not allow it.

MOST LIKELY

Removing a duplicate or test record that has picked up real child rows (accounts, orders).

FIRST CHECK

Find the child rows.

TRY IT ▸

MainframeKit assistant

What it means

The table you are deleting from is the parent in a referential constraint (a foreign key). At least one child row in another table still has the parent's key, and the constraint's delete rule is RESTRICT or NO ACTION, so Db2 will not leave those children pointing at nothing. The message names the relationship, which tells you which child table to look in.

A CASCADE or SET NULL rule on this relationship does not give -532 by itself, but a cascading delete that reaches a table further down whose rule is RESTRICT or NO ACTION does. Check the delete rule on every table below the parent. The rule is chosen when the foreign key is defined.

Typical causes

  • Removing a duplicate or test record that has picked up real child rows (accounts, orders).
  • A clean-up script that deletes parents before children.
  • A program that deletes a row it thinks is unused.

How to fix it

  1. Find the child rows.
  2. Decide what they should belong to. A duplicate parent usually means moving the children to the real parent with an UPDATE.
  3. Then delete the parent.
  4. Only delete children when the business confirms they are really unwanted.

Example

Illustrative only; names and values are invented.

DSNT408I SQLCODE = -532, ERROR:  THE RELATIONSHIP ACCTCUST RESTRICTS THE
         DELETION OF ROW WITH RID X'000000020D'
DSNT418I SQLSTATE   = 23504 SQLSTATE RETURN CODE