# SQLCODE -543: Delete blocked by a check constraint

Source: https://wndsoftware.com/mainframe-errors/sqlcode-543

### SQLCODE -543: Delete blocked by a check constraint

SQLCODE -543 (SQLSTATE 23511) means a DELETE on a parent table was rejected because a foreign key with ON DELETE SET NULL would set a dependent column to null, and a check constraint on that column doesn't allow null.


#### Other names for this code

Also written as `SQL -543`, `SQLCODE=-543`, `SQLCODE = -543`, `db2 -543`, `sqlstate 23511`, `SQL0543N`, `a row in a parent table cannot be deleted because the check constraint restricts the deletion`.


### 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

- Take the constraint name from the message and read its rule with `SELECT TBNAME, CHECKNAME, CHECKCONDITION FROM SYSIBM.SYSCHECKS WHERE CHECKNAME = 'EMPDEPTCK'`.
- 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.
- Get the foreign key columns from SYSIBM.SYSFOREIGNKEYS and confirm the check constraint uses one of them.
- 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.


### IBM documentation

[IBM documentation: -543](https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=codes-543). Use the documentation for your installed release.

[Report a correction to WND Software](https://wndsoftware.com/contact#message).

Technical reviewer: [Albert Joseph](https://wndsoftware.com/authors/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.


### Related codes

[SQLCODE -545 Row violates a check constraint Read →](https://wndsoftware.com/mainframe-errors/sqlcode-545)
[SQLCODE -532 Delete restricted by a foreign key Read →](https://wndsoftware.com/mainframe-errors/sqlcode-532)
[SQLCODE -530 Foreign key value has no parent Read →](https://wndsoftware.com/mainframe-errors/sqlcode-530)
[SQLCODE -723 Error in a triggered SQL statement Read →](https://wndsoftware.com/mainframe-errors/sqlcode-723)
[SQLCODE -407 Null value in a NOT NULL column Read →](https://wndsoftware.com/mainframe-errors/sqlcode-407)

### Questions


#### Why did Db2 let me define both rules?

Db2 checks the conflict when a DELETE actually needs to set the column to null, not when the constraints are created. So the problem can sit unnoticed until the first parent row with children is deleted.


#### Is -543 the same on Db2 for LUW?

Yes. Db2 for LUW reports SQL0543N with SQLSTATE 23511 for the same conflict.


#### How is -543 different from -532?

-532 means a RESTRICT or NO ACTION rule blocks the delete because child rows exist. -543 means the SET NULL rule would apply but a check constraint stops the null.

[Db2 SQLCODEs](https://wndsoftware.com/mainframe-errors/db2-sqlcodes) · [SQLCODE -542](https://wndsoftware.com/mainframe-errors/sqlcode-542) · [SQLCODE -545](https://wndsoftware.com/mainframe-errors/sqlcode-545)

[← All error codes](https://wndsoftware.com/mainframe-errors/)