How to cascade delete over many to many table

Question

I have a 3 tables that look like this:

On the foreign keys I have set cascade deletes. Right now, when I delete a record in the Folder table, only the related record in the FolderItem is deleted.

This is expected and correct.

What I would to accomplish is when I delete a record in the Folder table, the corresponding records in the FolderItem and the Item table should be deleted.

How do I solve this? By adding a trigger that deletes all instances of Item with the FolderID in question? Or is there any better solution?

Answer 1

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

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

Answer 2

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

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