// MySQL error reference

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

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_lines before orders.
  • 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 TABLE fails with 1452).
  • Columns that look equal but differ in collation or trailing spaces.

How to fix it

  1. Check whether the parent exists.

    SELECT id FROM orders WHERE id = 1001;
  2. Insert the parent first, or load tables in dependency order.

  3. 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;
  4. 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

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.