[ACCEPTED]-How to cascade delete over many to many table-cascading-deletes
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.
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.