MySQL Error 1451: Cannot Delete or Update a Parent Row

MySQL Error 1451 (23000, ER_ROW_IS_REFERENCED_2) means a DELETE or key-changing UPDATE would affect a parent row that is still referenced by a child row. The foreign-key constraint is preventing an orphaned reference. With InnoDB, RESTRICT and NO ACTION both reject the parent operation. See the MySQL 8.4 error reference and foreign-key actions.

Find the child rows that block the change

Suppose employees reference departments through department_id. Deleting a department fails while an employee still points to it:

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

CREATE TABLE employees (
  employee_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  department_id INT UNSIGNED NOT NULL,
  KEY ix_employees_department_id (department_id),
  CONSTRAINT fk_employees_department
    FOREIGN KEY (department_id)
    REFERENCES departments (department_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

If department 10 has employees, this statement returns Error 1451:

DELETE FROM departments
WHERE department_id = 10;

Query the child table before changing the parent:

SELECT employee_id, department_id
FROM employees
WHERE department_id = 10;

Replace 10 with the key shown in your error message. SHOW CREATE TABLE employees\G shows the constraint name and the configured ON DELETE and ON UPDATE actions.

Choose what should happen to the child rows

Keep the parent row if the children still need it. If the children should belong to a different department, verify that the destination exists and reassign them first:

UPDATE employees
SET department_id = 20
WHERE department_id = 10;

DELETE FROM departments
WHERE department_id = 10;

Delete child rows first only when removing them is correct for the application. If a parent deletion should always remove its dependent rows, define ON DELETE CASCADE; this automatically deletes the child rows too, so use it only when that behavior is intended. If the child rows should remain without a parent, ON DELETE SET NULL is an option only when the foreign-key column allows NULL.

For parent-key changes, ON UPDATE CASCADE can update child references automatically when the key changes. Choose referential actions from the data model, not just to suppress Error 1451.

Error 1451 is different from Error 1452, which occurs when a child row refers to a parent key that does not exist. See how to fix MySQL Error 1452 and the MySQL foreign-key guide. Browse all MySQL error troubleshooting guides.