Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses to delete a row because other rows point to it

ErrorERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Short answer

Error 1451 means at least one child row still references the parent row you are deleting or changing. InnoDB rejects it because the foreign key uses NO ACTION or RESTRICT, the default. Delete the child rows first, or change the constraint to ON DELETE CASCADE or SET NULL if that matches your data rules.

  • Applies to MySQL 8.4, MySQL Connector/J 9, Hibernate 6, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    The error message names the child table and the constraint. If your client hides it, ask InnoDB for the details of the last foreign key error.

    SHOW ENGINE INNODB STATUS\G
  2. 2Match what you see, then jump to its fix

    1. 1The message lists a child table and constraint name, and the delete works once those rows are goneChild rows still exist and the constraint uses the default actionFix
    2. 2You always delete the children by hand right before the parentThe business rule is that children go with the parentFix
    3. 3The error appears while restoring a dump, running LOAD DATA, or dropping a referenced tableYou are bulk-reloading or dropping tables in the wrong orderFix

What does the error look like?

  • ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
  • SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails

Cause 1 of 3

Child rows still exist and the constraint uses the default action

1Check

When ON DELETE is not written, the action is NO ACTION, which InnoDB treats like RESTRICT: the parent delete is rejected while any child row points to it. List the child rows for the parent id.

SELECT * FROM orders WHERE customer_id = 42;
SHOW CREATE TABLE orders\G

2Fix

Delete or reassign the child rows in the same transaction, then delete the parent. In Hibernate or JPA, do the same through the owning entity.

START TRANSACTION;
DELETE FROM orders WHERE customer_id = 42;
DELETE FROM customers WHERE id = 42;
COMMIT;

3Verify

The parent delete returns one affected row and no error.

SELECT COUNT(*) FROM customers WHERE id = 42;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The business rule is that children go with the parent

1Check

Decide per relationship. CASCADE deletes matching child rows automatically. SET NULL keeps them but clears the column, so that column must not be NOT NULL.

SHOW CREATE TABLE orders\G

2Fix

Recreate the foreign key with the action you want. Take a backup first, because CASCADE removes data silently.

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  ON DELETE CASCADE;

3Verify

Delete a test parent row and confirm its child rows are gone.

DELETE FROM customers WHERE id = 42;
SELECT COUNT(*) FROM orders WHERE customer_id = 42;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

You are bulk-reloading or dropping tables in the wrong order

1Check

foreign_key_checks is on by default. Turning it off is meant for exactly these jobs: dropping a referenced table, reloading tables in a different order, LOAD DATA, and some ALTER TABLE work.

SHOW VARIABLES LIKE 'foreign_key_checks';

2Fix

Switch checks off only for your own session, run the job, and switch them back on. MySQL does not re-check rows added while it was off, so verify the data yourself.

SET foreign_key_checks = 0;
-- run the reload or drop here
SET foreign_key_checks = 1;

3Verify

Look for child rows whose parent is missing.

SELECT o.id FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Write the ON DELETE action on every foreign key so the rule is visible in the schema.
  • Prefer soft deletes (a deleted_at column) for records other tables must keep referencing.
  • Never leave foreign_key_checks off in a connection pool; set it per session only.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.