An insert or update is rejected because the parent row does not exist (error 1452)
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`))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.
Fix it fast
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\G2Match what you see, then jump to its fix
- 1
The value in the failing column does not exist in the parent tableThe parent row has not been inserted yetFix - 2
The failing value is 0 or an empty string for an optional relationAn application sends 0 or an empty value instead of NULLFix - 3
The error appears on ALTER TABLE ... ADD FOREIGN KEY, not on an insertExisting rows are orphans while you add the constraintFix - 4
The error stops a restore or CSV import on the first child tableA data import loads tables in the wrong orderFix
- 1
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
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
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\GConfirmed by mariadb.com
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to create a foreign key (error 1215)
ERROR 1005 (HY000): Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")
Error 1215 is a generic label.
5 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix