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

What it means

A foreign key ties rows in a child table (here books) to a row in a parent table (authors). Error 1451 means you tried to DELETE a parent row, or change its key with UPDATE, while child rows still refer to it. The foreign key’s delete rule is RESTRICT or NO ACTION (the default), so the server refuses rather than leave the children pointing at nothing.

The part in brackets names the child table and the constraint, not the parent:

(`seo_mysql`.`books`, CONSTRAINT `fk_books_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))

So: rows in books still have author_id set to the row you’re deleting.

Common causes

1. Child rows still exist: orders for a customer, comments for a post.
2. Changing a primary key value that other tables use, without ON UPDATE CASCADE.
3. DROP TABLE on a parent. MySQL 8.4 reports error 3730 for this; MariaDB reports 1451 with no detail.
4. TRUNCATE on a parent, which fails with error 1701 even when no child rows exist.

How to fix it

Find what refers to the table

List every foreign key that points at the parent:

SELECT table_name, constraint_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_schema = 'seo_mysql' AND referenced_table_name = 'authors';

TABLE_NAME	CONSTRAINT_NAME	COLUMN_NAME	REFERENCED_COLUMN_NAME
books	fk_books_author	author_id	id

Then look at the child rows: SELECT * FROM books WHERE author_id = 1;

Delete the children first

In one transaction, so you never end up with half of it done:

START TRANSACTION;
DELETE FROM books WHERE author_id = 1;
DELETE FROM authors WHERE id = 1;
COMMIT;

Let the server do it: CASCADE or SET NULL

If deleting a parent should always delete its children (ON DELETE CASCADE) or detach them (ON DELETE SET NULL, which needs a nullable column), change the foreign key. You can’t drop and re-add a constraint under the same name in one statement, so give the new one a new name:

ALTER TABLE books
  DROP FOREIGN KEY fk_books_author,
  ADD CONSTRAINT fk_books_author_cascade FOREIGN KEY (author_id)
    REFERENCES authors (id) ON DELETE CASCADE;

With foreign_key_checks on, this copies the whole table and blocks writes while it runs. The rows already satisfy the same columns’ old constraint, so you can turn checks off for the session first, and it runs in place:

SET SESSION foreign_key_checks = 0;
ALTER TABLE books DROP FOREIGN KEY fk_books_author,
  ADD CONSTRAINT fk_books_author_cascade FOREIGN KEY (author_id)
    REFERENCES authors (id) ON DELETE CASCADE, ALGORITHM=INPLACE;
SET SESSION foreign_key_checks = 1;

Think before choosing CASCADE: one DELETE can then remove rows from many tables.

Keep the row instead

If the parent is referenced by records you must keep (invoices, audit rows), mark it inactive with a column such as deleted_at rather than deleting it.

Dropping or emptying a parent table

SET FOREIGN_KEY_CHECKS = 0 for your session lets DROP TABLE and TRUNCATE go through. The child rows then point at nothing, so only do it when you’re dropping or reloading the children too.

Reproduce it

On MySQL 8.4.11, with authors (one row, id 1) and books (one row with author_id 1) linked by fk_books_author:

DELETE FROM authors WHERE id = 1;
UPDATE authors SET id = 5 WHERE id = 1;

Both fail:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`seo_mysql`.`books`, CONSTRAINT `fk_books_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))

TRUNCATE and DROP TABLE on the parent:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`seo_mysql`.`books`, CONSTRAINT `fk_books_author`)
ERROR 3730 (HY000): Cannot drop table 'authors' referenced by a foreign key constraint 'fk_books_author' on table 'books'.

TRUNCATE failed with 1701 in the same way on a parent whose child table was empty.

MariaDB 11.4.13 gave the same 1451 for DELETE and UPDATE, a longer 1701 for TRUNCATE, and 1451 for DROP TABLE, without naming anything:

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

information_schema.referential_constraints showed the default delete rule as NO ACTION on MySQL and RESTRICT on MariaDB; both refused the delete straight away. Dropping and adding a constraint with the same name in one ALTER TABLE failed on both (MySQL: 1826, Duplicate foreign key constraint name; MariaDB: 1005, errno: 121). With a new name it worked, and DELETE FROM authors WHERE id = 1 then removed the author and, through ON DELETE CASCADE, its book. Without foreign_key_checks = 0, ALGORITHM=INPLACE was refused on both:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.

In Inlet

The structure editor shows a table’s constraints with their DDL and warns when a change rewrites the table. On connections you’ve protected, a DELETE without WHERE asks you to type the table’s name before it runs.