MySQL refuses to delete a row because other rows point to it
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint failsShort 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.
Fix it fast
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\G2Match what you see, then jump to its fix
- 1
The 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
You always delete the children by hand right before the parentThe business rule is that children go with the parentFix - 3
The error appears while restoring a dump, running LOAD DATA, or dropping a referenced tableYou are bulk-reloading or dropping tables in the wrong orderFix
- 1
What does the error look like?
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint failsSQLIntegrityConstraintViolationException: 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\G2Fix
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
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\G2Fix
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
An insert or update is rejected because the parent row does not exist (error 1452)
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row
Error 1452 means a row you insert or update points, through a foreign key, at a parent row that is not there.
4 causes, each with a fixDatabases
MySQL refuses to create a foreign key (error 1215)
ERROR 1005 (HY000): Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")
Error 1215 is a generic label.
5 causes, each with a fixDatabases
EntityNotFoundException when loading an entity that has a missing related row
ConstraintViolationException on an orphaned foreign key
EntityNotFoundException means Hibernate followed an id and no row was there.
3 causes, each with a fixDatabases
Hibernate fails on a CREATE or ALTER TABLE statement at startup
Error executing DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via JDBC Statement
Hibernate generated a CREATE, ALTER or DROP statement and MySQL refused it.
3 causes, each with a fixDatabases
MySQL Connector/J: EOFException, expected to read 4 bytes, read 0 bytes
Communications link failure
The MySQL server or something between you and it closed the connection, and the driver found out only when it tried to use it.
3 causes, each with a fixDatabases
Java cannot log in to MySQL 8: authentication plugin caching_sha2_password not supported
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
MySQL 8 accounts use the caching_sha2_password plugin by default, and Connector/J releases before 8.0.9 cannot use it.
3 causes, each with a fix