MySQL error 1451: Foreign key fails: cannot delete parent row
You tried to delete or change the key of a row that child rows still reference, and the foreign key doesn't cascade.
- Error number
1451- Name
ER_ROW_IS_REFERENCED_2- SQLSTATE
23000- Category
- Keys and constraints
What it means
With the default ON DELETE RESTRICT (or NO ACTION), MySQL won't delete a parent row, or change its key, while child rows point to it. The message ends with the constraint definition naming the child table.
Common causes
- Deleting a customer who still has orders.
- Dropping or truncating a parent table's rows before the child tables.
- Updating a primary key value that child rows reference.
How to fix it
-
Find the child rows that block the delete.
SELECT * FROM orders WHERE customer_id = 42;
-
Delete or reassign the children first, in one transaction.
START TRANSACTION; DELETE FROM orders WHERE customer_id = 42; DELETE FROM customers WHERE id = 42; COMMIT;
-
If child rows should always go with the parent, change the foreign key to cascade.
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer, ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;
Related errors
1452Foreign key fails: cannot add child row1217Cannot delete or update a parent row1701Can't truncate a table with foreign keys547SQL Server: Statement conflicted with a constraint23503PostgreSQL: foreign key constraint violationORA-02292Oracle: integrity constraint violated - child record found
More keys and constraints errors
1062Duplicate entry for key1061Duplicate key name1216Cannot add or update a child row1215Cannot add foreign key constraint3780Foreign key columns are incompatible1822Missing index for foreign key constraint1824Failed to open the referenced table1048Column cannot be null
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.