I have a 3 tables that look like this: 
我有一个这样的3个表:
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.
在外键上，我设置了级联删除。现在，当我删除文件夹表中的一条记录时，只删除FolderItem中的相关记录。
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.
我要完成的是，当我删除文件夹表中的一条记录时，相应的FolderItem和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?
我怎么解决这个问题?通过添加一个触发器来删除所有带有该FolderID的项目实例?或者有更好的解决方案吗?
3 个解决方案
#1
11
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.
你需要决定你想要系统的什么行为。您的需求听起来有点不正常，可能表明db模式设计中出现了错误。当相关文件夹被删除时，为什么要删除一个条目?如果还有另一个文件夹仍然与该项相关，因为它是多对多关系，该怎么办?在这种情况下，删除项实际上会导致项和折叠项之间的外键违反。如果项目确实属于一个特定的文件夹，也就是一个到多个关系的文件夹，那么您根本不需要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.
我想，最可能的情况是，如果没有与它相关的其他FolderItem条目，那么您希望删除该项目。在这种情况下，触发器是适当的解决方案，但是您需要确保在触发器逻辑中检查它。
#2
1
Your FK between folder item and item should also have cascaded deletes turned on.
在文件夹项和项之间的FK也应该有级联删除。
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.
更新:我读了你的评论和其他答案。绝对正确——我不可能正确地理解你的问题。触发器(或者更好的是代码中的一些业务逻辑)是您实现所需的最佳选择。
#3
-1
It can't delete the folteritem record because if u have m-to-m relationship, it's supposed that the folderitem can be related to another course too. so, if u delete on folder record, only the relation should be deleted, not the related folderitems. If all folderitems can only be related to only one folder, you should use the one-to-many relationship and set the cascade in the folderitem fk (fk -> pk in folder).
它不能删除folteritem记录，因为如果你有m到m的关系，它假设folderitem也可以与另一个课程相关。所以，如果u在文件夹记录上删除，应该只删除关系，而不删除相关的折叠项。如果所有的folderitems只能与一个文件夹相关，那么应该使用一对多关系，并在folderitem fk (fk -> pk)中设置cascade。
#1
11
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.
你需要决定你想要系统的什么行为。您的需求听起来有点不正常，可能表明db模式设计中出现了错误。当相关文件夹被删除时，为什么要删除一个条目?如果还有另一个文件夹仍然与该项相关，因为它是多对多关系，该怎么办?在这种情况下，删除项实际上会导致项和折叠项之间的外键违反。如果项目确实属于一个特定的文件夹，也就是一个到多个关系的文件夹，那么您根本不需要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.
我想，最可能的情况是，如果没有与它相关的其他FolderItem条目，那么您希望删除该项目。在这种情况下，触发器是适当的解决方案，但是您需要确保在触发器逻辑中检查它。
#2
1
Your FK between folder item and item should also have cascaded deletes turned on.
在文件夹项和项之间的FK也应该有级联删除。
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.
更新:我读了你的评论和其他答案。绝对正确——我不可能正确地理解你的问题。触发器(或者更好的是代码中的一些业务逻辑)是您实现所需的最佳选择。
#3
-1
It can't delete the folteritem record because if u have m-to-m relationship, it's supposed that the folderitem can be related to another course too. so, if u delete on folder record, only the relation should be deleted, not the related folderitems. If all folderitems can only be related to only one folder, you should use the one-to-many relationship and set the cascade in the folderitem fk (fk -> pk in folder).
它不能删除folteritem记录，因为如果你有m到m的关系，它假设folderitem也可以与另一个课程相关。所以，如果u在文件夹记录上删除，应该只删除关系，而不删除相关的折叠项。如果所有的folderitems只能与一个文件夹相关，那么应该使用一对多关系，并在folderitem fk (fk -> pk)中设置cascade。