How to make sure redundant data is deleted in a many-to-many relationship

Question

Hi folks,

I'm trying to make sure some data is auto-deleted when there's no more references using cascade deletes. I'll explain with a fake database based on Stack Overflow.

I have a Post table. Each post has zero to many Tags.

So it should look like:

Post <-> PostTags <-> Tags

eg.

Post 1 has tags 'A', 'B', 'C' Post 2 has tags 'C', 'D'.

now, what i'm doing is that when i delete all the tags for post 2 (eg. DELETE FROM PostTags WHERE PostId = 2), I would like tag 'D' to also be deleted because no one else if referencing it. I thought cascade deletes would handle this, but of course it's only if u cascade down from Tag->PostTags or Post->PostTags.

I'm not sure how to handle this.

I'm afraid that people will suggest using a trigger :( (extra complexity to the system).

Thoughts?

Note: DB is MS Sql2008.

Answer 1

Unfortunately, you won't be able to use cascades here. Cascades don't work when you have a many-to-many relationships because you won't have a singular parent that references the child (in this case, you have multiple PostTags entries which could reference the Tags table).

Triggers would be a way to do this, but I wouldn't recommend it. Rather, I would suggest that in your data layer, after you deleted the entries in the PostTags table, delete the Tags which are not referenced anymore. If you have a stored procedure which does the deleting of the entry, you might want to consider doing this there (it would be relatively simple to do as well).

Answer 2

I think you have to use an on delete trigger on your Posts table, that checks the tags of the post being deleted and deletes them if they aren't being referenced (in the PostTags table).

Before deleting your post, save this recordset:

SELECT tagID, count(*) from PostsTags WHERE postId = 2 group by tagID;

Then, after you delete it, loop through this recordsoet and if count(*) is = 1, then delete the tag.