[ACCEPTED]-How to cascade delete over many to many table-cascading-deletes

Accepted answer
Score: 16

You need to decide what behavior you want 18 exactly with the system. Your requirement 17 sounds a bit abnormal and might indicate 16 a mistake in db schema design. Why do you 15 want to delete an Item when a related Folder 14 is deleted? What if there is another Folder 13 still related to that item, since it is 12 a many-to-many relationship? In that case, deleting 11 the Item will actually cause foreign key 10 violation between Item and FolderItem. If 9 the Items actually do belong under a specific 8 Folder, aka one to many relationship, you 7 will not need the FolderItem table at all.

I 6 guess the mostly likely case is you want 5 to delete the Item if there is no other 4 FolderItem entries related to it. In that 3 case, trigger is the appropriate solution, but 2 you need to make sure you are checking for 1 it in the trigger logic.

Score: 2

Your FK between folder item and item should 8 also have cascaded deletes turned on.

UPDATE:
I 7 read your comment, and the alternative answer. Absolutely 6 right - I couldn't have read your question 5 properly. Assuming simple one-many relationships 4 I'd be right, but with a many-many it's 3 not so simple. Triggers (or better still, some 2 business logic in your code) are your best 1 bet to achieve what you want.

More Related questions