0
mysql> delete from faculty where fid in ( select fid from class);

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (ass1.class, CONSTRAINT class_ibfk_1 FOREIGN KEY (fid) REFERENCES faculty (fid))

This is throwing the mentioned error. What should I do?

Roger
  • 7,535
  • 5
  • 41
  • 63
Stuper Duper
  • 21
  • 1
  • 4
  • You have cascade delete on? If not, you can't delete the parent before deleting (or moving) the child row. – Roger Aug 14 '16 at 20:04

3 Answers3

1

It is dangerous to force this without knowing what the consequences are.

This error is happening because there is another table that has a foreign key association with something you're trying to delete. What do you want to happen to those rows in other tables that rely on the deleted data? Here are a few options:

  • If you want to delete those rows that rely on the data you're trying to delete, look into cascade deletion.
  • If you decide the data that relies on the data you need to delete should no longer have a foreign key constraint, you can drop the constraint.
  • If you don't want either of these things, consider not actually deleting your data, but instead using a boolean column to flag rows as "inactive". Then, you can filter on the flag when retrieving data if needed, while still maintaining old records that rely on that "obsolete" data.
Community
  • 1
  • 1
Karin
  • 8,404
  • 25
  • 34
0

You're trying to delete from parent table. Which is probably why you're getting an error.

Hadrian Blackwater
  • 437
  • 2
  • 5
  • 15
0

Either set up the FK to cascade deletes:

YourForeignIDField INT NOT NULL REFERENCES ParentTable.ID ON UPDATE CASCADE ON DELETE CASCADE

Or set up the foreign ID to accept Nulls and cascade to null:

YourForeignIDField INT NULL REFERENCES ParentTable.ID ON UPDATE CASCADE ON DELETE SET NULL

Or delete all children first:

DELETE FROM ChildTable WHERE ForeignID = YourForgeignID;
DELETE FROM ParentTable WHERE ID = YourID;
jleach
  • 7,410
  • 3
  • 33
  • 60