Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

An insert or update is rejected because the parent row does not exist (error 1452)

ErrorERROR 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`))

Short answer

Error 1452 means a row you insert or update points, through a foreign key, at a parent row that is not there. Every non-NULL foreign key value must match a parent value. Read the failing constraint in the InnoDB status output, then insert the parent first, fix the value, or clean the orphan rows before adding the constraint.

  • Applies to MariaDB 11.4 LTS, MySQL 8.4 LTS, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask InnoDB which constraint failed and on which values. The section LATEST FOREIGN KEY ERROR is only printed after a foreign key error has happened.

    SHOW ENGINE INNODB STATUS\G
  2. 2Match what you see, then jump to its fix

    1. 1The value in the failing column does not exist in the parent tableThe parent row has not been inserted yetFix
    2. 2The failing value is 0 or an empty string for an optional relationAn application sends 0 or an empty value instead of NULLFix
    3. 3The error appears on ALTER TABLE ... ADD FOREIGN KEY, not on an insertExisting rows are orphans while you add the constraintFix
    4. 4The error stops a restore or CSV import on the first child tableA data import loads tables in the wrong orderFix

What does the error look like?

  • 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`))
  • SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row

Cause 1 of 4

The parent row has not been inserted yet

1Check

Look up the value you are inserting in the parent table named in the REFERENCES part of the message.

SELECT id FROM customers WHERE id = 42;

2Fix

Insert the parent row first, or change the child value to one that exists. Order matters in scripts and seeders: parents before children.

INSERT INTO customers (id, name) VALUES (42, 'Acme');
INSERT INTO orders (customer_id, total) VALUES (42, 19.90);

3Verify

The child insert now succeeds.

SELECT * FROM orders WHERE customer_id = 42;

Confirmed by mariadb.com

Did this fix it?

Cause 2 of 4

An application sends 0 or an empty value instead of NULL

1Check

A NULL foreign key value needs no parent, but 0 is a real value that must exist in the parent. Look for default values the app fills in.

SELECT COUNT(*) FROM orders WHERE customer_id = 0;

2Fix

Store NULL when there is no related row, and make the column nullable.

ALTER TABLE orders MODIFY customer_id INT UNSIGNED NULL;
UPDATE orders SET customer_id = NULL WHERE customer_id = 0;

3Verify

No child row holds a 0 any more.

SELECT COUNT(*) FROM orders WHERE customer_id = 0;

Confirmed by mariadb.com

Did this fix it?

Cause 3 of 4

Existing rows are orphans while you add the constraint

1Check

Find child rows that have no parent with an anti-join.

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;

2Fix

Delete or repair those rows, then add the constraint. Take a backup before deleting.

DELETE o 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;
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

3Verify

The ALTER TABLE finishes without an error.

SHOW CREATE TABLE orders\G

Confirmed by mariadb.com

Did this fix it?

Cause 4 of 4

A data import loads tables in the wrong order

1Check

A dump that loads a child table before its parent fails on every row until the parent exists. Foreign key checks can be switched off for one session.

SHOW VARIABLES LIKE 'foreign_key_checks';

2Fix

Switch the check off for the import session only, load everything, switch it on again, then look for orphans.

SET foreign_key_checks = 0;
SOURCE dump.sql;
SET foreign_key_checks = 1;

3Verify

Run the orphan query from the previous cause on each child table; it must return no rows.

SHOW VARIABLES LIKE 'foreign_key_checks';

Confirmed by mariadb.com

Did this fix it?

How do I stop it coming back?

  • Insert parents before children in seeders, fixtures and migrations.
  • Use NULL, not 0, for a missing optional relation.
  • Switch foreign_key_checks off only inside an import session, and run the orphan query after it.
  • Check for orphan rows before every migration that adds a foreign key.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.