Skip to content

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

✗ Broken code
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 rows
✓ Fixed code
await 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 rows

Adding 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

← Back to language