什么时候/为什么在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?

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

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.

我最关心的是每个场景的具体例子，最好是那些已经成功使用过它们的人。

15 个解决方案

#1

Summary of what I've seen so far:

总结我到目前为止所看到的:

• Some people don't like cascading at all.
• 有些人根本不喜欢层叠。

Cascade Delete

• 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.
• 当关系的语义涉及到一个专有的“是”描述时，级联删除可能是有意义的。例如，一个OrderLine记录是它的父订单的一部分，并且OrderLine永远不会在多个订单之间共享。如果订单要消失，那么OrderLine也应该消失，没有订单的行也会成为问题。
• 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.
• 级联删除的典型示例是SomeObject和SomeObjectItems，在这些示例中，如果没有相应的主记录，项记录将永远存在，这是毫无意义的。
• 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.
• 如果您正在保存历史或使用“软/逻辑删除”，而只将删除的位列设置为1/true，则不应该使用Cascade Delete。

Cascade Update

• Cascade Update may make sense when you use a real key rather than a surrogate key (identity/autoincrement column) across tables.
• 跨表使用真正的键而不是代理键(identity/autoincrement列)时，级联更新可能是有意义的。
• 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.
• 级联更新最好与唯一约束结合使用。

When To Use Cascading

• You may want to get an extra strong confirmation back from the user before allowing an operation to cascade, but it depends on your application.
• 在允许操作级联之前，您可能希望从用户那里得到一个特别强的确认，但这取决于您的应用程序。
• Cascading can get you into trouble if you set up your foreign keys wrong. But you should be okay if you do that right.
• 如果设置错误的外键，级联会给您带来麻烦。但如果你做得对，你应该没事。
• It's not wise to use cascading before you understand it thoroughly. However, it is a useful feature and therefore worth taking the time to understand.
• 在你完全理解它之前，使用级联是不明智的。然而，这是一个有用的特性，因此值得花时间去理解。

#2

Foreign keys are the best way to ensure referential integrity of a database. Avoiding cascades due to being magic is like writing everything in assembly because you don't trust the magic behind compilers.

外键是确保数据库引用完整性的最佳方法。由于具有魔力而避免级联，就像在程序集中编写所有内容一样，因为您不相信编译器背后的魔力。

What is bad is the wrong use of foreign keys, like creating them backwards, for example.

不好的是错误地使用外键，比如反向创建它们。

Juan Manuel's example is the canonical example, if you use code there are many more chances of leaving spurious DocumentItems in the database that will come and bite you.

Juan Manuel的例子是一个典型的例子，如果您使用代码，那么在数据库中留下伪造文档的机会将会更多，而这些文档将会咬到您。

Cascading updates are useful, for instance, when you have references to the data by something that can change, say a primary key of a users table is the name,lastname combination. Then you want changes in that combination to propagate to wherever they are referenced.

级联更新是有用的，例如，当您通过一些可以更改的东西引用数据时，比如用户表的主键是名称、lastname组合。然后，您希望将该组合中的更改传播到它们所引用的任何地方。

@Aidan, That clarity you refer to comes at a high cost, the chance of leaving spurious data in your database, which is not small. To me, it's usually just lack of familiarity with the DB and inability to find which FKs are in place before working with the DB that foster that fear. Either that, or constant misuse of cascade, using it where the entities were not conceptually related, or where you have to preserve history.

@Aidan，您提到的清晰性代价很高，在您的数据库中留下虚假数据的机会并不小。对我来说，这通常只是缺乏对DB的熟悉，以及在使用DB之前无法找到哪个FKs已经就位，从而助长了这种恐惧。要么就是不断地滥用级联，在实体与概念无关的地方使用它，或者在必须保存历史的地方使用它。

#3

I never use cascading deletes.

我从不使用级联删除。

If I want something removed from the database I want to explicitly tell the database what I want taking out.

如果我想从数据库中删除一些内容，我想明确地告诉数据库我想要删除的内容。

Of course they are a function available in the database and there may be times when it is okay to use them, for example if you have an 'order' table and an 'orderItem' table you may want to clear the items when you delete an order.

当然，它们是数据库中可用的函数，有时可以使用它们，例如，如果您有一个“order”表和一个“orderItem”表，您可能希望在删除订单时清除这些项。

I like the clarity that I get from doing it in code (or stored procedure) rather than 'magic' happening.

我喜欢通过代码(或存储过程)而不是“魔法”来实现。

For the same reason I am not a fan of triggers either.

出于同样的原因，我也不喜欢触发器。

Something to notice is that if you do delete an 'order' you will get '1 row affected' report back even if the cascaded delete has removed 50 'orderItem's.

需要注意的是，如果你删除了一个“order”，你会得到一个“受影响的一行”报告，即使这个“级联删除”删除了50个“orderItem”。

#4

I work a lot with cascading deletes.

我经常使用级联删除。

It feels good to know whoever works against the database might never leave any unwanted data. If dependencies grow I just change the constraints in the diagramm in Management Studio and I dont have to tweak sp or dataacces.

知道对数据库工作的人可能永远不会留下任何不想要的数据，这感觉很好。如果依赖项增加，我只需更改Management Studio中的示意图中的约束，而无需修改sp或dataacces。

That said, I have 1 problem with cascading deletes and thats circular references. This often leads to parts of the database that have no cascading deletes.

也就是说，级联删除和循环引用有一个问题。这通常导致数据库中没有级联删除的部分。

#5

One example is when you have dependencies between entities... ie: Document -> DocumentItems (when you delete Document, DocumentItems don't have a reason to exist)

一个例子是当你在实体之间有依赖…ie:文档->文档(当你删除文档时，文档没有理由存在)

#6

I do a lot of database work and rarely find cascade deletes useful. The one time I have used them effectively is in a reporting database that is updated by a nightly job. I make sure that any changed data is imported correctly by deleting any top level records that have changed since the last import, then reimport the modified records and anything that relates to them. It save me from having to write a lot of complicated deletes that look from the bottom to the top of my database.

我做了大量的数据库工作，很少发现级联删除有用。我有效地使用它们的一次是在一个报告数据库中，该数据库由一个夜间作业更新。通过删除自上次导入以来发生更改的任何顶层记录，确保正确导入任何更改的数据，然后重新导入修改后的记录和与之相关的任何内容。它使我不必编写大量从数据库底部到顶部的复杂删除操作。

I don't consider cascade deletes to be quite as bad as triggers as they only delete data, triggers can have all kinds of nasty stuff inside.

我不认为级联删除和触发器一样糟糕，因为它们只删除数据，触发器内部可能有各种各样的问题。

In general I avoid real Deletes altogether and use logical deletes (ie. having a bit column called isDeleted that gets set to true) instead.

一般来说，我完全避免真正的删除，并使用逻辑删除(ie)。有一个名为isDeleted的位列，并将其设置为true)。

#7

Use cascade delete where you would want the record with the FK to be removed if its referring PK record was removed. In other words, where the record is meaningless without the referencing record.

如果要删除FK的引用PK记录，则使用cascade delete。换句话说，如果没有引用记录，记录就没有意义。

I find cascade delete useful to ensure that dead references are removed by default rather than cause null exceptions.

我发现cascade delete对于确保默认删除死引用很有用，而不是导致空异常。

#8

ON Delete Cascade:

When you want rows in child table to be deleted If corresponding row is deleted in parent table.

当您想要删除子表中的行时，在父表中删除相应的行。

If on cascade delete isn't used then an error will be raised for referential integrity.

如果不使用级联删除，那么将会为引用完整性提高一个错误。

ON Update Cascade:

When you want change in primary key to be updated in foreign key

当你想要更改主键时，要用外键更新。

#9

One reason to put in a cascade delete (rather than doing it in the code) is to improve performance.

输入级联删除(而不是在代码中执行)的一个原因是提高性能。

Case 1: With a cascade delete

案例1:使用级联删除

 DELETE FROM table WHERE SomeDate < 7 years ago;

Case 2: Without a cascade delete

案例2:没有级联删除

 FOR EACH R IN (SELECT FROM table WHERE SomeDate < 7 years ago) LOOP
   DELETE FROM ChildTable WHERE tableId = R.tableId;
   DELETE FROM table WHERE tableId = R.tableid;
   /* More child tables here */
 NEXT

Secondly, when you add in an extra child table with a cascade delete, the code in Case 1 keeps working.

其次，当您添加带有级联删除的子表时，情形1中的代码将继续工作。

I would only put in a cascade where the semantics of the relationship is "part of". Otherwise some idiot will delete half of your database when you do:

我只会把关系的语义叫做“一部分”。否则，当你这样做的时候，一些笨蛋会删除你一半的数据库:

DELETE FROM CURRENCY WHERE CurrencyCode = 'USD'

#10

I try to avoid deletes or updates that I didn't explicitly request in SQL server.

我尽量避免在SQL server中显式请求的删除或更新。

Either through cascading or through the use of triggers. They tend to bite you in the ass some time down the line, either when trying to track down a bug or when diagnosing performance problems.

要么通过级联，要么通过使用触发器。他们往往会在接下来的一段时间里对你百般刁难，不管是在跟踪bug还是诊断性能问题的时候。

Where I would use them is in guaranteeing consistency for not very much effort. To get the same effect you would have to use stored procedures.

我使用它们的地方是为了保证一致性，而不是花很多力气。要获得相同的效果，您必须使用存储过程。

#11

I, like everyone else here, find that cascade deletes are really only marginally helpful (it's really not that much work to delete referenced data in other tables -- if there are lot of tables, you simply automate this with a script) but really annoying when someone accidentally cascade deletes some important data that is difficult to restore.

我,和其他人一样,发现级联删除只有所帮助(这真的不是很多工作要删除引用其他表中的数据——如果有很多表,您只需用一个脚本自动执行这个)但是真的讨厌当别人不小心级联删除一些重要数据,很难恢复。

The only case where I'd use is if the data in the table table is highly controlled (e.g., limited permissions) and only updated or deleted from through a controlled process (like a software update) that has been verified.

我使用的唯一情况是，如果表表中的数据是高度受控的(例如，受限的权限)，并且只通过受控过程(如软件更新)进行更新或删除，这一点已经得到验证。

#12

I have had it in the past where DBAs and/or "Company Policy" have prohibited the use of "On Delete Cascade" (and others) purely because of a bad experience in the past (in once case, a guy wrote three triggers which ended up calling one another -- 3 days to recover resulted in a total ban on triggers -- because of the actions of one idjit).

我有过去,dba和/或“公司政策”已经禁止使用“删除级联”(和其他人)纯粹因为糟糕的经历在过去(在一次情况下,最后一个人写了三个触发器调用另一个——3天恢复了全面禁止触发的行为——因为一个idjit)。

Of course, in some instances, Triggers will be needed instead of "On Delete cascade", when some child data needs to be preserved, but in other cases, its perfectly valid to use the On Delete cascade method.

当然，在某些情况下，当需要保留一些子数据时，将需要触发器而不是“On Delete cascade”，但在其他情况下，使用On Delete cascade方法是完全有效的。

The Developer should be able to make the decision based upon what the development is and what the spec says. A carpet ban based on a bad experience should not be the criteria. On Delete cascade will capture ALL the children, a custom written trigger/store procedure may not if its not coded correctly.

开发人员应该能够根据开发的内容和规范的内容做出决定。基于糟糕经历的地毯禁令不应成为标准。在删除级联将捕获所有子进程，自定义编写的触发器/存储过程可能不会，如果它没有正确编码。

The "Never use" thought process is draconian at best. A judgement call needs to be made each and every time, and changes made as the business model changes.

“永不使用”的思想过程充其量是严厉的。每次都需要进行判断调用，并在业务模型更改时进行更改。

Isnt this what "Development" is all about???

这难道不是“发展”的意义所在吗?

#13

A deletion or update to S that removes a foreign-key value found in some tuples of R can be handled in one of three ways:

删除或更新S以删除在R的某些元组中发现的外键值，可以通过以下三种方式处理:

1. Rejection
2. 拒绝
3. Propagation
4. 传播
5. nullification.
6. 无效。

Propagation is referred to as cascading.

传播称为级联。

There are two cases:

有两种情况:

‣ If a tuple in S was deleted, delete the R tuples that referred to it.

‣如果一个元组被删除,删除R称为元组。

‣ If a tuple in S was updated, update the value in the R tuples that refer to it.

‣如果一个元组在更新,更新值R元组中引用它。

#14

If you're working on a system with many different modules in different versions, it can be very helpful, if the cascade deleted items are part of / owned by the PK holder. Else, all modules would require immediate patches to clean up their dependent items before deleting the PK owner, or the foreign key relation would be omitted completely, possibly leaving tons of garbage in the system if cleanup is not performed correctly.

如果您正在开发一个拥有不同版本的许多不同模块的系统，如果级联删除项是PK holder的一部分或所有项，那么这将非常有用。否则，在删除PK所有者之前，所有模块都需要立即的补丁来清理它们的相关项，或者完全忽略外键关系，如果清理操作不正确，可能会在系统中留下大量的垃圾。

I just introduced cascade delete for a new intersection table between two already existing tables (the intersection to delete only), after cascade delete had been discouraged from for quite some time. It's also not too bad if data gets lost.

我刚刚为两个已经存在的表(只有要删除的交集)之间的新交集表引入了cascade delete，在相当长的一段时间内，cascade delete一直被禁止。如果数据丢失，也不是很糟糕。

It is, however, a bad thing on enum-like list tables: somebody deletes entry 13 - yellow from table "colors", and all yellow items in the database get deleted. Also, these sometimes get updated in a delete-all-insert-all manner, leading to referential integrity totally omitted. Of course it's wrong, but how will you change a complex software which has been running for many years, with introduction of true referential integrity being at risk of unexpected side effects?

然而，在类似enum的列表表中，这是一件坏事:有人从表“colors”中删除条目13 - yellow，数据库中的所有黄色条目将被删除。而且，这些有时会以完全插入的方式更新，导致引用完整性被完全忽略。当然，这是错误的，但是如果引入了真正的引用完整性，那么您将如何更改一个运行多年的复杂软件呢?

Another problem is when original foreign key values shall be kept even after the primary key has been deleted. One can create a tombstone column and an ON DELETE SET NULL option for the original FK, but this again requires triggers or specific code to maintain the redundant (except after PK deletion) key value.

另一个问题是，即使在主键被删除之后，仍然要保留原有的外键值。可以创建一个tombstone列，并为原始FK创建一个ON DELETE SET NULL选项，但这同样需要触发器或特定代码来维护冗余(除PK删除之后)的键值。

#15

Cascade deletes are extremely useful when implementing logical super-type and sub-type entities in a physical database.

级联删除在实现物理数据库中的逻辑超类型和子类型实体时非常有用。

When separate super-type and sub-type tables are are used to physically implement super-types/sub-types (as opposed to rolling up all sub-type attributes into a single physical super-type table), there is a one-to-one relationship between these tables and the issue then becomes how to keep the primary keys 100% in sync between these tables.

当独立的超类和子类型表是用于身体实现super-types /亚型(而不是卷起所有子类型属性到一个物理超类表),这些表之间有一对一的关系,这个问题就变成了如何让这些表之间的主键100%同步。

Cascade deletes can be a very useful tool to:

级联删除是一个非常有用的工具:

1) Make sure that deleting a super-type record also deletes the corresponding single sub-type record.

1)确保删除超级类型记录也删除相应的单个子类型记录。

2) Make sure that any delete of a sub-type record also deletes the super-type record. This is achieved by implementing an "instead-of" delete trigger on the sub-type table that goes and deletes the corresponding super-type record, which, in turn, cascade deletes the sub-type record.

2)确保任何子类型记录的删除也删除超类型记录。这是通过在子类型表上实现“instead-of”删除触发器实现的，该触发器将删除相应的超类型记录，而后者又将级联删除子类型记录。

Using cascade deletes in this manner ensures that no orphan super-type or sub-type records ever exist, regardless of whether you delete the super-type record first or the sub-type record first.

以这种方式使用cascade delete可以确保不存在任何孤儿超类型或子类型记录，无论您是先删除超类型记录还是先删除子类型记录。