何时/为什么在 SQL Server 中使用级联?

2021-12-26 00:00:00 foreign-keys sql-server database-design cascade rdbms

在SQL Server中设置外键时，在什么情况下应该在删除或更新时级联，其背后的原因是什么?

When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it?

这可能也适用于其他数据库.

This probably applies to other databases as well.

我最重要的是寻找每个场景的具体示例，最好是成功使用它们的人.

I'm looking most of all for concrete examples of each scenario, preferably from someone who has used them successfully.

推荐答案

我目前看到的总结:

  • 有些人根本不喜欢级联.
  • 当关系的语义可能涉及独占的是的一部分"描述时，级联删除可能有意义.例如，OrderLine 记录是其父订单的一部分，OrderLine 永远不会在多个订单之间共享.如果 Order 消失，OrderLine 也应该消失，而没有 Order 的行会出现问题.
  • 级联删除的规范示例是 SomeObject 和 SomeObjectItems，如果没有相应的主记录，项目记录永远存在是没有任何意义的.
  • 如果您要保留历史记录或使用软/逻辑删除"(其中您只将已删除的位列设置为 1/true)，则应该不使用级联删除.
  • Cascade Delete may make sense when the semantics of the relationship can involve an exclusive "is part of" description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
  • The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.
  • You should not use Cascade Delete if you are preserving history or using a "soft/logical delete" where you only set a deleted bit column to 1/true.
  • 当您跨表使用真实键而不是代理键(身份/自动增量列)时，级联更新可能更有意义.
  • 级联更新的典型示例是当您拥有可变外键(例如可以更改的用户名)时.
  • 您应该不将级联更新用于标识/自动增量列的键.
  • 级联更新最好与唯一约束结合使用.
  • Cascade Update may make sense when you use a real key rather than a surrogate key (identity/autoincrement column) across tables.
  • The canonical example for Cascade Update is when you have a mutable foreign key, like a username that can be changed.
  • You should not use Cascade Update with keys that are Identity/autoincrement columns.
  • Cascade Update is best used in conjunction with a unique constraint.
  • 在允许操作级联之前，您可能希望从用户那里获得额外的强有力的确认，但这取决于您的应用程序.
  • 如果外键设置错误，级联会给您带来麻烦.但是，如果你做得对，你应该没问题.
  • 在完全理解之前使用级联是不明智的.但是，这是一项有用的功能，因此值得花时间了解.

相关文章