如何在多对多表上级联删除 [英] How to cascade delete over many to many table

问题描述

我有一个看起来像这样的 3 个表:
_{(来源:InsomniacGeek.com)}

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

在外键上我设置了级联删除.现在，当我删除 Folder 表中的一条记录时，只有 FolderItem 中的相关记录被删除.

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.

这是预期且正确的.

我要完成的是，当我删除Folder表中的一条记录时，应该删除FolderItem和Item表中对应的记录.

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.

我该如何解决这个问题?通过添加一个触发器来删除具有相关 FolderID 的所有 Item 实例?或者有什么更好的解决方案?

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?

推荐答案

你需要决定你想要的系统行为.您的要求听起来有点不正常，可能表明数据库架构设计中存在错误.为什么在删除相关文件夹时要删除项目?如果有另一个文件夹仍然与该项目相关，因为它是多对多关系怎么办?在这种情况下，删除 Item 实际上会导致 Item 和 FolderItem 之间的外键冲突.如果项目确实属于特定文件夹，也就是一对多关系，则您根本不需要 FolderItem 表.

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.

我想最有可能的情况是，如果没有其他 FolderItem 条目与之相关，您想删除该项目.在这种情况下，触发器是合适的解决方案，但您需要确保在触发器逻辑中检查它.

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.

这篇关于如何在多对多表上级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！