困境：级联删除或连接删除
这不是一个具体的问题，而是一个普遍的疑问。
当您必须对具有 1:M 关系的多个表进行删除时，是使用级联删除进行 FK 约束还是在删除语句中连接表更好。
我有一个旧项目，对相关表有单独的删除语句，有几次某些语句未执行，数据完整性受到损害。我必须在两者之间做出决定，所以我在想什么是更好的解决方案。
还可以选择创建存储过程或事务。
所以我正在寻找意见或建议......？
It's not a specific question, more a general wondering.
When you have to make a delete on multiple tables in a 1:M relationship, is it better to make a FK constraint with a cascade delete or join the tables in the delete statement.
I had an old project that had separate delete statements for related tables, and a few times some of the statements were not executed and data integrity was compromised. I had to make a decision between the two, so I was thinking a bit what would be a better solution.
There is also an option to make a stored procedure or a transaction.
So I am looking for an opinion or advice...?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我想说使用级联删除更安全。如果您决定使用联接，则必须记住每次从父表中删除任何内容时都使用它们；即使您有足够的纪律来做到这一点，您也无法确定您的同事或人员将来会支持您的软件。此外，多次对有关表关系的此类知识进行编码违反了 DRY 原则。
如果您使用级联删除，则没有人需要记住任何内容，并且子行将始终根据需要被删除。
I'd say it's safer to use a cascade delete. If you decide to use joins, you have to remember to use them every time you delete anything from parent table; and even if you're disciplined enough to do that, you can't be sure about your coworkers or people who will support your software in the future. Also, encoding such knowledge about table relationships more than once violates DRY principle.
If you use a cascade delete though, nobody has to remember anything, and child rows will always be deleted as needed.
如果您的数据库为其定义了正确的 RI，那么就不应该出现任何数据完整性受损的情况。所有相关表都应具有声明性 RI，这意味着您无法删除仍有子项的父项。
另外，如果您的代码有时仅删除某些行，那么这就是糟糕的编码和糟糕的测试。这些类型的操作应该是单个事务。您使用存储过程的建议是解决该问题的好方法，并且非常标准。
正如已经提到的，级联触发器存在删除某人不打算删除的行的危险。考虑到有时人们可能会从应用程序外部的某个地方访问您的数据，尤其是在修复数据问题时。当有人不小心尝试删除错误的父级并收到 RI 错误时，这很好。当他们不小心尝试删除错误的父表时，它不仅删除了该父表，还删除了其他 5 个表中的 20 个子表，这很糟糕。
而且，级联删除是非常隐蔽的。如果开发人员正在为父级编写删除代码，那么他们应该知道他们必须使用删除存储过程来照顾子级。让开发人员不针对此进行编码，得到错误并修复他的代码（或者意识到他实际上不想执行所有这些删除操作）比让开发人员进行删除并拥有要好得多在代码生效之前，没有人意识到它正在杀死儿童。
IMO，我更愿意让我的开发人员了解该应用程序，而不是让他们更容易对其一无所知。
If your database has proper RI defined for it then there shouldn't be any case of compromised data integrity. All of your related tables should have declarative RI, which means that you can't delete a parent while it still has children.
Also, if you have code that is only deleting some of the rows at times then that is poor coding and poor testing. These kinds of actions should be a single transaction. Your suggestion of using a stored procedure is a great approach for solving that problem and is pretty standard.
As has already been mentioned, cascading triggers have the danger of deleting rows that someone did not intend to delete. Consider that sometimes people might be accessing your data from somewhere outside of your application, especially when fixing data issues. When someone accidentally tries to delete the wrong parent and gets an RI error that's good. When they accidentally try to delete the wrong parent and it not only deletes that parent but 20 children in 5 other tables, that's bad.
Also, cascading deletes are very hidden. If a developer is coding a delete for the parent then they should know that they have to use the delete stored procedure to take care of children. It's much preferable to have a developer not code against that, get an error, and fix his code (or realize that he doesn't really want to do all of that deleting) than it is to have a developer throw in a delete and have no one realize that it's killing off children until the code has gone live.
IMO, I prefer to have my developers knowledgeable about the application rather than make it easier for them to remain ignorant of it.
级联删除会导致很多问题，因此非常危险。我不会推荐它的使用。首先，假设我需要删除具有数百万条子记录的记录。您可以锁定数据库并使其在数小时内无法使用。据我所知，很少有数据库管理员会允许在他们的数据库中使用级联删除。
其次，如果您定义了 FK，它对数据完整性没有帮助。子记录仍然存在的删除将会失败，这是一件好事。例如，如果客户有现有订单，我希望客户删除失败。不加考虑地使用级联删除（根据我的经验通常是这样）可能会导致删除您确实不想删除的内容。
Cascade delete causes lots of issues and thus is extremely dangerous. I would not recommend its use. In the first place, suppose I need to delete record that has millions of child records. You could lock up the database and make it unusable for hours. I know of very few dbas who will permit cascade delete to be used in their databases.
Next, it does not help with data integrity if you have defined the FKs. A delete with child records still existant will fail which is a good thing. I want the customer delete to fail if he has existing orders for instance. Cascade delete used thoughtlessly (as it usually is in my experience) can cause things to be deleted that you really don't want to delete.
两者都用！
“联合”手动删除通常更能避免死锁和其他争用问题，因为您可以将删除分解为更小的工作单元。如果确实存在争用，那么找到冲突的原因肯定更容易。
如上所述，“删除级联”将绝对保证引用完整性。
因此，请同时使用两者——显式删除连接 SQL 中的“子级”以避免死锁和性能问题。但请启用“级联删除”以捕获您错过的任何内容。由于当您删除父级时不应该留下任何子级，因此这不会花费您任何费用，除非您在删除时犯了错误，在这种情况下，为了维护引用完整性而付出的成本是值得的。
Use both!
"Joined" manual deletes are usually better for avoiding deadlocks and other contention problems as you can break up the deletes into smaller units of work. If you do have contention its definitely easier to find the cause of the conflict.
As stated "Delete Cascade" will absolutely guarantee referential integrity.
So use both -- do explicit deletes of the "children" in joined sqls to avoid deadlocks and performance problems. But leave "CASCADE DELETE" enabled to catch anything you missed. As there should be no children left when you come to delete the parent this won't cost you anything, unless, you made a mistake with your deletes, in which case the cost is worth it to maintain your referential integrity.