Cannot delete or update a parent row: a foreign key constraint fails

What this means

Error 1451 (SQLSTATE 23000) is raised by InnoDB when a DELETE or UPDATE on a parent table would leave orphaned rows in a child table that references it via a FOREIGN KEY with RESTRICT or NO ACTION. The operation is blocked to preserve referential integrity.

Why it happens

1. 1Attempting to DELETE a parent row that is referenced by one or more child rows
2. 2Attempting to UPDATE the primary key of a parent row referenced by child rows
3. 3Deleting rows in the wrong order during a bulk data operation
4. 4A CASCADE rule is missing on the foreign key definition

How to reproduce

Deleting a customer who has existing orders.

DELETE FROM customers WHERE id = 42;
-- orders table has: FOREIGN KEY (customer_id) REFERENCES customers(id)

Fix 1: Delete child rows first

When the child rows should also be deleted.

DELETE FROM orders WHERE customer_id = 42;
DELETE FROM customers WHERE id = 42;

Fix 2: Use ON DELETE CASCADE on the foreign key

When deleting a parent should automatically delete all children.

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

Fix 3: Use ON DELETE SET NULL to soft-orphan child rows

When orders should be retained even after the customer is deleted.

ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
ALTER TABLE orders ADD CONSTRAINT orders_ibfk_1
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE SET NULL;

What not to do

Dangerous variant

Sources

Confidence assessment

See also