MySQL "Cannot add or update a child row": Why Foreign Key Rejects?
Distinction between 1452 and 1451, charset and data type mismatch, finding orphan rows, and safe bulk data loading procedures.
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptoms
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails▸Diagnosis & Resolution
- 1Find Orphan Rows:
SELECT c.* FROM child_table c
LEFT JOIN parent_table p ON p.id = c.parent_id
WHERE c.parent_id IS NOT NULL AND p.id IS NULL LIMIT 50;- 1Check Data Types & Collation:
Columns must match exactly (INT UNSIGNED vs INT or utf8mb4 vs latin1 will fail).
- 1Safe Bulk Ingestion:
Wrap large dumps in SET FOREIGN_KEY_CHECKS = 0; and ensure restoration with SET FOREIGN_KEY_CHECKS = 1; followed by integrity verification.
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
MySQL ERROR 1045 Access denied: root Password Reset Guide
Four causes of MySQL 1045, meaning of using password: YES/NO, and safe credential recovery via --init-file.
MySQL Deadlock ERROR 1213: Graph Analysis and Permanent Prevention
Root causes of MySQL 1213 deadlock, parsing LATEST DETECTED DEADLOCK output, gap lock concurrency issues, and resilient application retry design.
MySQL Lock wait timeout exceeded: Finding Blocking Sessions
Finding the blocking transaction using performance_schema, safe KILL decision matrices, and permanent locking reduction.