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.
当您必须以1:M关系对多个表进行删除时,最好是使用级联删除制作FK约束还是连接delete语句中的表。
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...?
所以我正在寻找意见或建议......?
4 个解决方案
#1
4
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.
如果您的数据库具有为其定义的正确RI,则不应存在任何受损数据完整性的情况。您的所有相关表都应具有声明性RI,这意味着在父级仍具有子级时您无法删除父级。
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.
正如已经提到的,级联触发器具有删除某人不打算删除的行的危险。考虑到有时人们可能会从应用程序之外的某个位置访问您的数据,尤其是在修复数据问题时。当有人意外地尝试删除错误的父级并获得一个好的RI错误。当他们意外地尝试删除错误的父级时,它不仅删除了那个父级而且还删除了其他5个表中的20个子级,这很糟糕。
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.
IMO,我更喜欢让我的开发人员了解应用程序,而不是让他们更容易对它不知情。
#2
7
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.
我想说使用级联删除更安全。如果您决定使用连接,则必须记住每次从父表中删除任何内容时都使用它们;即使你有足够的纪律来做到这一点,你也无法确定你的同事或将来会支持你的软件的人。此外,不止一次地编码关于表关系的这种知识违反了DRY原则。
If you use a cascade delete though, nobody has to remember anything, and child rows will always be deleted as needed.
如果您使用级联删除,则没有人必须记住任何内容,并且将始终根据需要删除子行。
#3
4
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.
级联删除会导致很多问题,因此非常危险。我不建议使用它。首先,假设我需要删除具有数百万个子记录的记录。您可以锁定数据库并使其无法使用数小时。我知道很少有dbas允许在他们的数据库中使用级联删除。
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.
接下来,如果已定义FK,则无法帮助确保数据完整性。删除子记录仍然存在将失败,这是一件好事。如果他有现有订单,我希望客户删除失败。不经意地使用级联删除(通常是根据我的经验)会导致删除您真正不想删除的内容。
#4
0
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.
因此,使用两者 - 在连接的sqls中显式删除“children”以避免死锁和性能问题。但是启用“CASCADE DELETE”可以捕获您错过的任何内容。因为当你来删除父母时不应该留下任何孩子,这将不会花费你任何东西,除非,你的删除错误,在这种情况下,成本值得维持你的参照完整性。
#1
4
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.
如果您的数据库具有为其定义的正确RI,则不应存在任何受损数据完整性的情况。您的所有相关表都应具有声明性RI,这意味着在父级仍具有子级时您无法删除父级。
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.
正如已经提到的,级联触发器具有删除某人不打算删除的行的危险。考虑到有时人们可能会从应用程序之外的某个位置访问您的数据,尤其是在修复数据问题时。当有人意外地尝试删除错误的父级并获得一个好的RI错误。当他们意外地尝试删除错误的父级时,它不仅删除了那个父级而且还删除了其他5个表中的20个子级,这很糟糕。
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.
IMO,我更喜欢让我的开发人员了解应用程序,而不是让他们更容易对它不知情。
#2
7
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.
我想说使用级联删除更安全。如果您决定使用连接,则必须记住每次从父表中删除任何内容时都使用它们;即使你有足够的纪律来做到这一点,你也无法确定你的同事或将来会支持你的软件的人。此外,不止一次地编码关于表关系的这种知识违反了DRY原则。
If you use a cascade delete though, nobody has to remember anything, and child rows will always be deleted as needed.
如果您使用级联删除,则没有人必须记住任何内容,并且将始终根据需要删除子行。
#3
4
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.
级联删除会导致很多问题,因此非常危险。我不建议使用它。首先,假设我需要删除具有数百万个子记录的记录。您可以锁定数据库并使其无法使用数小时。我知道很少有dbas允许在他们的数据库中使用级联删除。
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.
接下来,如果已定义FK,则无法帮助确保数据完整性。删除子记录仍然存在将失败,这是一件好事。如果他有现有订单,我希望客户删除失败。不经意地使用级联删除(通常是根据我的经验)会导致删除您真正不想删除的内容。
#4
0
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.
因此,使用两者 - 在连接的sqls中显式删除“children”以避免死锁和性能问题。但是启用“CASCADE DELETE”可以捕获您错过的任何内容。因为当你来删除父母时不应该留下任何孩子,这将不会花费你任何东西,除非,你的删除错误,在这种情况下,成本值得维持你的参照完整性。