SQLCODE -543: Delete blocked by a check constraint

What it means

Two rules on the dependent table contradict each other. The foreign key says that when a parent row is deleted, the matching foreign key columns in the child rows become null. That is the SET NULL delete rule. A check constraint on one of those same columns says the value can't be null. Db2 can't follow both rules at once, so it refuses the DELETE.

Db2 accepts both definitions when they are created. The conflict only shows up when a DELETE actually reaches child rows. The whole DELETE is rejected and the parent and child tables stay as they were. The message names the check constraint.

Most check constraints don't block nulls. Db2 treats a condition that comes out unknown as satisfied, and a comparison with null is unknown. So a rule like CHECK (DEPT_ID > 0) accepts a null. -543 happens when the condition is written so that a null makes it false, such as CHECK (DEPT_ID IS NOT NULL).

Common causes

• Someone added a check constraint to force every child row to have a parent, without changing the foreign key's SET NULL delete rule.
• The foreign key was later changed to SET NULL while a not-null style check constraint was already on the column.
• A DELETE cascades through several tables and reaches a table further down that has the SET NULL rule and the conflicting constraint.
• The data model was built by more than one team, and the two rules were added for different reasons.

How to find the problem

1. Take the constraint name from the message and read its rule with SELECT TBNAME, CHECKNAME, CHECKCONDITION FROM SYSIBM.SYSCHECKS WHERE CHECKNAME = 'EMPDEPTCK'.
2. List the foreign keys that reference the parent table with SELECT CREATOR, TBNAME, RELNAME, DELETERULE FROM SYSIBM.SYSRELS WHERE REFTBCREATOR = 'PROD' AND REFTBNAME = 'DEPARTMENT'. DELETERULE N means SET NULL, C means CASCADE, R means RESTRICT and A means NO ACTION.
3. Get the foreign key columns from SYSIBM.SYSFOREIGNKEYS and confirm the check constraint uses one of them.
4. If the DELETE cascades, repeat the SYSRELS query for each table in the chain.

How to fix it

• Decide which rule the business really wants. If a child row must always have a parent, change the delete rule to RESTRICT or NO ACTION and move or delete the child rows before the parent.
• If child rows may lose their parent, drop or rewrite the check constraint so it accepts null.
• For a one-off delete, update the child rows to point at another parent first, then delete.
• Changing a delete rule means dropping and adding the foreign key. Plan it with the DBA, because adding a foreign key to a table with data can leave the table space in CHECK-pending until CHECK DATA runs.

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.