sql server - How to cascade delete over many to many table

Question

I have a 3 tables that look like this:
_{(source: InsomniacGeek.com)}

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

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.