📋

Description

This error occurs when you attempt to delete or update a row in a 'parent' table that has related entries in a 'child' table, and a foreign key constraint prevents this action. It signifies a violation of referential integrity, where the database is protecting the consistency of your data by disallowing operations that would orphan child records.

💬

Error Message

Cannot delete or update a parent row: a foreign key constraint fails

🔍

Known Causes

3 known causes

⚠️

Parent Row Still Referenced

The primary key of the parent row you are trying to delete or update is still referenced by foreign key(s) in one or more child tables.

⚠️

Missing ON DELETE/UPDATE Action

The foreign key constraint definition lacks an `ON DELETE` or `ON UPDATE` clause (like `CASCADE` or `SET NULL`) to automatically handle child rows upon parent modification.

⚠️

Application Logic Violation

An application attempted to modify or delete a parent record without first addressing or disassociating its related child records, violating database integrity rules.

🛠️

Solutions

5 solutions available

1. Delete Child Records First easy

Remove dependent records before parent

1

Find child records

SELECT * FROM orders WHERE user_id = 123;

2

Delete children first

DELETE FROM orders WHERE user_id = 123;
DELETE FROM users WHERE id = 123;

2. Use ON DELETE CASCADE medium

Auto-delete children when parent is deleted

1

Add CASCADE to foreign key

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;

2

Now parent delete works automatically

DELETE FROM users WHERE id = 123;  -- Also deletes user's orders

3. Use ON DELETE SET NULL medium

Set FK to NULL when parent is deleted

1

Modify FK to SET NULL

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE SET NULL;

4. Reassign Children to Different Parent easy

Move children before deleting parent

1

Update children to new parent

-- Move orders to different user before deleting
UPDATE orders SET user_id = 1 WHERE user_id = 123;

-- Now safe to delete
DELETE FROM users WHERE id = 123;

5. Fix UPDATE of Parent Key medium

Handle changing parent's primary key

1

Use ON UPDATE CASCADE

ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE
  ON DELETE RESTRICT;

2

Now updating parent key propagates to children

UPDATE users SET id = 999 WHERE id = 123;  -- orders.user_id also updates

🔗

Related Errors

5 related errors

MySQL Error 1217: Foreign Key Constraint Failure