// MySQL error reference

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
Error message
Cannot delete or update a parent row: a foreign key constraint fails%s

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

  1. Find the child rows that block the delete.

    SELECT * FROM orders WHERE customer_id = 42;
  2. 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;
  3. 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

More keys and constraints errors

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.

// fewer errors, faster fixes

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.