0

I'm trying to think of a way to automatically remove a row from a table, when the last foreign key reference to it, is dropped. - That is I'm looking for a sort of garbage collection.

I've been trying different approaches, alike reference counting, however I was thinking if it would be achievable using the on delete and on update actions?

Skeen
  • 4,614
  • 5
  • 41
  • 67
  • have you tried triggers? write a trigger so when every row with foreign key reference to main row is deleted system check if any other row with that foreign key reference to the main row exists or not.if not delete the main row – Aref Anafgeh May 30 '17 at 05:43

2 Answers2

2

It sounds like you're on the right path. I would look into cascade. Cascading is pretty powerful so you might also want to look into soft deletes.

Community
  • 1
  • 1
Justin Lucas
  • 2,301
  • 14
  • 22
  • I like the idea with soft deletes, however I'm going to have a many-to-one relationship, such that I have many entries in table A, that have a foreign key to the same entry in table B. - And when the last FK from table A to an entry in table B is removed. I want to delete that object form Table B (possibly though use of softdeletes). - As such it's lot alike reference counting in C++, a lot of references to the same object, and when the last one is gone, I want the object deleted. – Skeen Dec 07 '11 at 19:58
  • Will I have to do this using triggers? – Skeen Dec 07 '11 at 20:38
  • No you can do it using the on delete clause like in Jan's answer. – Justin Lucas Dec 07 '11 at 23:39
  • Then I guess I'm not really sure how to? – Skeen Dec 08 '11 at 08:22
2

I'd use

ON DELETE CASCADE

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Jan Vorcak
  • 19,261
  • 14
  • 54
  • 90