Incident Response

MySQL Error 1452: Cannot Add or Update a Child Row (Foreign Key Constraint Fails)

Error 1452 means a row points at a parent that does not exist. The fix is usually one missing insert or a load in the wrong order. The dangerous fix is turning foreign_key_checks off and forgetting about it. Here is how to find the cause, clean the data, and add constraints on large tables safely.

· 13 min read · AG Data Team

MySQL error 1452, Cannot add or update a child row: a foreign key constraint fails, is InnoDB refusing to store a row whose foreign key value has no matching row in the parent table. Nothing is broken: the constraint is doing its job. The useful question is why your application, import or migration is trying to write a reference to something that is not there.

Read the whole message first

mysql> INSERT INTO orders (id, customer_id, total) VALUES (1001, 777, 49.90);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`)
REFERENCES `customers` (`id`))

The text after the generic part names the child table, the constraint, the child column and the parent table and column. That is everything you need to start. The error is ER_NO_REFERENCED_ROW_2, SQLSTATE 23000 (MySQL error reference). Its sibling, error 1451 Cannot delete or update a parent row, is the same constraint seen from the other side: you are deleting or changing a parent that still has children.

If you see the shorter error 1216 without the constraint details, the user running the statement lacks table-level privileges on the parent tables, so MySQL hides the parent information. Run the diagnosis as a user that can see both tables.

The usual causes

  1. The parent row really is missing. A typo in an ID, a customer deleted earlier, a test fixture that never created the parent, or an ID from another environment.
  2. Wrong insert order. The application inserts the order before the customer, or an ETL job loads child tables before parent tables.
  3. Adding a constraint to dirty data. ALTER TABLE ... ADD FOREIGN KEY with checks enabled validates existing rows, and in our experience a single orphan anywhere in the table is enough to make the whole ALTER fail with 1452. On a table that has lived without constraints for years, there are always orphans.
  4. Partial restores and data copies. Restoring one table from last night's backup into a database where the parent table moved on, or copying a subset of tables to staging.

Diagnose in three queries

-- 1. which constraint, which columns (or read the error text)
SHOW CREATE TABLE orders\G

-- every foreign key that points somewhere, per schema
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'shop' AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

-- 2. does the parent row exist?
SELECT id FROM customers WHERE id = 777;

-- 3. details of the most recent foreign key error
SHOW ENGINE INNODB STATUS\G   -- section LATEST FOREIGN KEY ERROR

For InnoDB, the LATEST FOREIGN KEY ERROR section of SHOW ENGINE INNODB STATUS shows details of the most recent foreign key error (foreign key docs). For constraint problems while creating tables (error 150), it is the first place to look too.

When the cause is existing data, list the orphans:

-- child rows whose parent is missing (orphans)
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL
LIMIT 100;

Fixing it properly

foreign_key_checks = 0 is not a fix. While checks are off, InnoDB accepts any value. Turning them back on does not scan the table, so every orphan written in the meantime stays there, undetected, until a report, a join or a later ALTER TABLE trips over it (MySQL manual). Use it only in a session you control, and always follow it with an orphan check.

Foreign key requirements that cause related errors

On large tables: what changes

On a test database with ten thousand rows, all of the above takes seconds. On a production table with hundreds of millions of rows, each step needs a plan.

-- on a large table: walk the child primary key in ranges, ideally on a replica
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.id >= 0 AND o.id < 1000000
  AND o.customer_id IS NOT NULL
  AND c.id IS NULL;
-- next range: 1000000 .. 2000000, and so on

-- once the data is clean: add the constraint without a table copy
SET SESSION foreign_key_checks = 0;
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
  REFERENCES customers (id),
  ALGORITHM=INPLACE, LOCK=NONE;
SET SESSION foreign_key_checks = 1;

Key takeaways

MySQL InnoDB Foreign keys Errors
Work with us

Orphan rows or a failed restore?

We clean up referential integrity problems on production MySQL: orphan audits on billion-row tables, safe constraint rollouts and restores that load in the right order.

Get in touch More from the blog