ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

What it means

A foreign key says every value in a column of one table (the child, here books.author_id) must exist in a column of another (the parent, authors.id). Error 1452 means your INSERT or UPDATE on the child would have stored a value with no matching parent row, so the server refused it.

The message spells out the constraint:

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

That’s the child table, the constraint’s name, the child column and the parent table and column. NULL is always allowed in a nullable foreign key column: it means “no parent”, and isn’t checked.

Common causes

1. The parent row doesn’t exist: a wrong id, an id from another environment, or a parent that was deleted.
2. 0 or an empty string instead of NULL. Code that sends 0 for “no author” asks for an author with id 0.
3. Rows loaded in the wrong order: children before their parents in an import or seed script.
4. The parent is in another transaction that hasn’t committed. Your insert waits for it; if that transaction rolls back, you get 1452.
5. Adding a foreign key to a table that already has orphans. The ALTER TABLE checks every row and fails with 1452, naming a temporary table such as #sql-1_6ee1.

How to fix it

Check the parent value

Take the value you were inserting and look for it in the parent:

SELECT id FROM authors WHERE id = 2;

No row: insert the parent first, in the same transaction if both are new, or correct the id.

Use NULL for “no parent”

INSERT INTO books (id, author_id, title) VALUES (12, NULL, 'Anonymous');

The column must be nullable. If your code or ORM turns a missing value into 0, fix it there.

Find orphaned rows

Before adding a constraint, or after data was loaded with checks off:

SELECT b.*
FROM books b
LEFT JOIN authors a ON a.id = b.author_id
WHERE b.author_id IS NOT NULL AND a.id IS NULL;

Delete those rows, point them at a real parent, or set them to NULL, then add the foreign key.

Loading data in any order

mysqldump and mariadb-dump already wrap their output in SET FOREIGN_KEY_CHECKS=0 and restore it at the end, so a dump restores in any order. For your own scripts:

SET FOREIGN_KEY_CHECKS = 0;
-- load the tables
SET FOREIGN_KEY_CHECKS = 1;

Turning checks back on doesn’t re-check what you loaded. Only do this with data you know is consistent, and run the orphan query above afterwards.

Reproduce it

On MySQL 8.4.11:

CREATE TABLE authors (id int PRIMARY KEY, name varchar(50));
CREATE TABLE books (id int PRIMARY KEY, author_id int, title varchar(100),
  CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors (id));
INSERT INTO authors VALUES (1, 'Ann');
INSERT INTO books VALUES (10, 1, 'First');

INSERT INTO books VALUES (11, 2, 'Second');
UPDATE books SET author_id = 99 WHERE id = 10;

Both statements fail with the same message:

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

INSERT INTO books VALUES (12, NULL, 'Anonymous') succeeds. With checks off, an orphan got in, and the LEFT JOIN query found it. Adding a foreign key to a table that already held an orphan:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seo_mysql`.`#sql-1_6ee1`, CONSTRAINT `fk_books2_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))

With one session holding an uncommitted INSERT INTO authors VALUES (2, 'Bea'), a second session’s INSERT INTO books VALUES (20, 2, 'Waits') waited about two seconds until the first rolled back, then failed with 1452.

MariaDB 11.4.13 gave identical messages; its ALTER TABLE names the temporary table #sql-alter-1-6d7d.

In Inlet

When an insert or update fails, Inlet shows the server’s message with a hint. The structure editor shows a table’s foreign keys with the DDL behind them, so you can see which parent column a value has to exist in.