I would like to know when to actually use delete cascade or delete restrict AND update cascade or update restrict. I'm pretty confused using them or applying in my database.
我想知道什么时候实际使用删除级联或删除限制和更新级联或更新限制。我很困惑在我的数据库中使用它们或应用它们。
2 个解决方案
#1
3
Using the cascading operators on foreign key constraints is a bit of a hot topic.
在外键约束上使用级联操作符是一个热门话题。
In theory, if you know for a fact that deleting a parent object will automatically also mean deleting all its children objects, then cascading delete on the link between child and parent table might make sense.
理论上，如果您知道删除父对象也将自动删除所有子对象，那么在子表和父表之间的链接上进行级联删除可能是有意义的。
Imagine a "machine" that consists of "parts". If your logic says that if the machine is deleted, all the parts making up that machine are also to be purged from the database, then you could use a cascading delete option on the foreign key link between the parts table and the machine table.
想象一个由“零件”组成的“机器”。如果您的逻辑说，如果机器被删除，那么组成该机器的所有部件也将从数据库中清除，那么您可以在部件表和机器表之间的外键链接上使用级联删除选项。
However: this can be a bit tricky, especially if you end up having a whole chain of tables that are linked with this option. Therefore, a lot of developers tend to prefer to handle this in their own data access code, rather than define it in the database.
然而:这可能有点棘手，尤其是当您最终拥有与此选项相关联的整个表链时。因此，许多开发人员倾向于在自己的数据访问代码中处理这个问题，而不是在数据库中定义它。
The update cascade is typically used when a primary key on a parent table changes - in order to update all related child tables and rows to reflect that change. This is typically considered a database code smell - the better option here is to make sure the primary key never changes, so that this cascading update is never needed - e.g. by introducing an artificial "surrogate" key column into your table, which has no business-related meaning and thus never gets updated.
更新级联通常在父表上的主键发生更改时使用，以便更新所有相关的子表和行以反映该更改。这通常被认为是一个数据库代码味道,更好的选择是确保主键不会改变,所以这种级联更新不需要——例如通过引入人为的“*”键列进你的表,没有业务相关的意义,因此从来没有得到更新。
Does that help at all? Any particular detail you're still unclear on ?
这有帮助吗?你还有什么特别的细节吗?
My take is this: while this is a great idea in theory, most developers don't really use this in "real live" - most developers will choose to handle this in data access code, which gives them full and explicit control over what gets deleted (or updated).
我的理解是:虽然这在理论上是一个很好的想法，但大多数开发人员并没有真正在“真实生活”中使用它——大多数开发人员会选择在数据访问代码中处理它，这使他们能够完全和明确地控制删除(或更新)的内容。
#2
0
As well as what marc_s says..
就像marc_s所说的。
I've found that CASCADE options can sometimes generate "dummy" updates to a child table when there was no need thus prolonging a transaction, using more locks, using more resources etc.
我发现，当不需要时，级联选项有时可以对子表生成“虚拟”更新，从而延长事务的时间，使用更多的锁，使用更多的资源等等。
#1
3
Using the cascading operators on foreign key constraints is a bit of a hot topic.
在外键约束上使用级联操作符是一个热门话题。
In theory, if you know for a fact that deleting a parent object will automatically also mean deleting all its children objects, then cascading delete on the link between child and parent table might make sense.
理论上，如果您知道删除父对象也将自动删除所有子对象，那么在子表和父表之间的链接上进行级联删除可能是有意义的。
Imagine a "machine" that consists of "parts". If your logic says that if the machine is deleted, all the parts making up that machine are also to be purged from the database, then you could use a cascading delete option on the foreign key link between the parts table and the machine table.
想象一个由“零件”组成的“机器”。如果您的逻辑说，如果机器被删除，那么组成该机器的所有部件也将从数据库中清除，那么您可以在部件表和机器表之间的外键链接上使用级联删除选项。
However: this can be a bit tricky, especially if you end up having a whole chain of tables that are linked with this option. Therefore, a lot of developers tend to prefer to handle this in their own data access code, rather than define it in the database.
然而:这可能有点棘手，尤其是当您最终拥有与此选项相关联的整个表链时。因此，许多开发人员倾向于在自己的数据访问代码中处理这个问题，而不是在数据库中定义它。
The update cascade is typically used when a primary key on a parent table changes - in order to update all related child tables and rows to reflect that change. This is typically considered a database code smell - the better option here is to make sure the primary key never changes, so that this cascading update is never needed - e.g. by introducing an artificial "surrogate" key column into your table, which has no business-related meaning and thus never gets updated.
更新级联通常在父表上的主键发生更改时使用，以便更新所有相关的子表和行以反映该更改。这通常被认为是一个数据库代码味道,更好的选择是确保主键不会改变,所以这种级联更新不需要——例如通过引入人为的“*”键列进你的表,没有业务相关的意义,因此从来没有得到更新。
Does that help at all? Any particular detail you're still unclear on ?
这有帮助吗?你还有什么特别的细节吗?
My take is this: while this is a great idea in theory, most developers don't really use this in "real live" - most developers will choose to handle this in data access code, which gives them full and explicit control over what gets deleted (or updated).
我的理解是:虽然这在理论上是一个很好的想法，但大多数开发人员并没有真正在“真实生活”中使用它——大多数开发人员会选择在数据访问代码中处理它，这使他们能够完全和明确地控制删除(或更新)的内容。
#2
0
As well as what marc_s says..
就像marc_s所说的。
I've found that CASCADE options can sometimes generate "dummy" updates to a child table when there was no need thus prolonging a transaction, using more locks, using more resources etc.
我发现，当不需要时，级联选项有时可以对子表生成“虚拟”更新，从而延长事务的时间，使用更多的锁，使用更多的资源等等。