Orphaned foreign key: referencing deleted parent
Quick summary
Child rows reference parent IDs that no longer exist — the foreign key constraint is missing or was disabled.
Why this happens
A parent row was deleted but the foreign key constraint was not enforced to cascade or restrict
The foreign key constraint was disabled or not created when the table was set up
A data import inserted child rows with parent IDs that do not exist in the parent table
Minimal example
await db.query('DELETE FROM orders WHERE id = 1'); // parent deleted
// Child rows in order_items still reference order_id = 1 — orphaned!
await db.query('SELECT * FROM order_items WHERE order_id = 1'); // returns orphaned rowsawait db.query('ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE');
await db.query('DELETE FROM orders WHERE id = 1'); // cascades to order_items
await db.query('SELECT * FROM order_items WHERE order_id = 1'); // returns 0 rowsAdding a foreign key constraint with ON DELETE CASCADE automatically deletes child rows when the parent is deleted, preventing orphans.
How to diagnose
Query for orphaned child rows: SELECT * FROM child WHERE parent_id NOT IN (SELECT id FROM parent)
Check the table definition for foreign key constraints: SHOW CREATE TABLE child
Review the delete operations on the parent table to see if cascading was configured
How to fix
Add a foreign key constraint with ON DELETE CASCADE or ON DELETE RESTRICT
Delete or update the orphaned child rows to reference valid parent IDs or set them to NULL
Fix the data import process to validate parent IDs before inserting child rows
How to prevent
Always define foreign key constraints when creating tables with referential relationships
Use ON DELETE CASCADE or ON DELETE SET NULL based on the desired behavior when parents are deleted
Related Resources
Related Glossary
Related Lessons
- Constraints
Learn about foreign keys.