MySQL Error 1452: Fix Cannot Add or Update a Child Row

With the default foreign_key_checks = 1, MySQL Error 1452 (SQLSTATE 23000, ER_NO_REFERENCED_ROW_2) means an INSERT or UPDATE tried to put a foreign-key value in a child table without a matching key in the parent table. The server rejects the change to preserve referential integrity. See the MySQL error reference.

Example: the parent key does not exist

In this example, each employee row must reference an existing department:

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  department_id INT NOT NULL,
  CONSTRAINT fk_employees_department
    FOREIGN KEY (department_id)
    REFERENCES departments (department_id)
) ENGINE = InnoDB;

If department 10 has not been created, this insert fails with Error 1452:

INSERT INTO employees (employee_id, department_id)
VALUES (101, 10);

Create or locate the intended parent row first, then insert the child row:

INSERT INTO departments (department_id, department_name)
VALUES (10, 'Engineering');

INSERT INTO employees (employee_id, department_id)
VALUES (101, 10);

MySQL requires each non-NULL foreign-key value in a child row to match a candidate key in the parent table. The same check applies when an UPDATE changes a child row’s foreign-key value. See MySQL foreign-key constraints.

Find the missing parent key

Take the database, table, constraint, and column names from the error message, then check the exact key value:

SELECT department_id
FROM departments
WHERE department_id = 10;

Replace 10 with the value from the failing INSERT or UPDATE. If no row is returned, check for a wrong ID, a missing parent insert, or an import that loaded child rows before parent rows. If the parent should already exist, check that the child uses the right database and referenced column.

For data imported while foreign-key checks were disabled, find existing orphan rows with an anti-join:

SELECT e.employee_id, e.department_id
FROM employees AS e
LEFT JOIN departments AS d
  ON d.department_id = e.department_id
WHERE e.department_id IS NOT NULL
  AND d.department_id IS NULL;

Insert or restore the correct parent rows, or repair the child keys according to the data model. Do not create dummy parent rows just to silence the error.

Avoid disabling foreign-key checks as a routine fix

Disabling foreign_key_checks can be useful for a controlled bulk reload, but it is not a repair for inconsistent data. Setting it back to 1 does not scan rows inserted while checks were off. Validate the imported rows with an anti-join before using them. See the MySQL foreign_key_checks system variable.

Error 1452 is raised when a child row points to a missing parent. Error 1451 is different: it occurs when a parent row cannot be deleted or updated because child rows still reference it; see how to fix MySQL Error 1451. If MySQL rejects the foreign-key definition itself, see how to fix MySQL Error 1215. For the constraint rules and referential actions, see the MySQL foreign-key guide.