MySQL error 1452: Foreign key fails: cannot add child row
An INSERT or UPDATE sets a foreign key column to a value with no matching row in the parent table. Insert the parent first or fix the value.
- Error number
1452- Name
ER_NO_REFERENCED_ROW_2- SQLSTATE
23000- Category
- Keys and constraints
What it means
A foreign key says every value in the child column must exist in the parent table's referenced column. 1452 means the row you're writing points at a parent that isn't there. The message ends with the full constraint definition, which names both tables and columns.
Common causes
- Inserting child rows before their parent rows, for example loading
order_linesbeforeorders. - A typo or stale id in the foreign key column.
- Adding a foreign key to a table whose existing data already has orphans (the
ALTER TABLEfails with 1452). - Columns that look equal but differ in collation or trailing spaces.
How to fix it
-
Check whether the parent exists.
SELECT id FROM orders WHERE id = 1001;
-
Insert the parent first, or load tables in dependency order.
-
Before adding a foreign key, find orphaned rows.
SELECT l.* FROM order_lines l LEFT JOIN orders o ON o.id = l.order_id WHERE o.id IS NULL;
-
For a bulk load where order can't be controlled, turn checks off for the session, then verify there are no orphans before turning them back on. Rows loaded while checks are off are not re-checked later.
SET foreign_key_checks = 0; -- load data SET foreign_key_checks = 1;
Reproduce it safely
CREATE TABLE p (id INT PRIMARY KEY); CREATE TABLE c (id INT PRIMARY KEY, p_id INT, FOREIGN KEY (p_id) REFERENCES p (id)); INSERT INTO c VALUES (1, 99); -- ERROR 1452 (23000) DROP TABLE c, p;
Related errors
1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1215Cannot add foreign key constraint1062Duplicate entry for key547SQL Server: Statement conflicted with a constraint23503PostgreSQL: foreign key constraint violationORA-02291Oracle: integrity constraint violated - no parent keySQLITE_CONSTRAINT_FOREIGNKEYSQLite: FOREIGN KEY constraint failed
More keys and constraints errors
1061Duplicate key name1217Cannot delete or update a parent row3780Foreign key columns are incompatible1822Missing index for foreign key constraint1824Failed to open the referenced table1048Column cannot be null3819Check constraint is violated1071Specified key was too long
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.