外键级联多个路径和循环有什么问题? [英] What is the problem with foreign key cascade multiple paths and cycles?

问题描述

在 SQL Server 2005 中，我刚刚收到了臭名昭著的错误消息:

In SQL Server 2005 I just struck the infamous error message:

在 YYY 表上引入 FOREIGN KEY 约束 XXX 可能会导致循环或多个级联路径.指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束.

Introducing FOREIGN KEY constraint XXX on table YYY may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

现在，StackOverflow 有几个关于此错误消息的主题，所以我已经有了解决方案(在我的情况下，我将不得不使用触发器)，但我很好奇为什么会出现这样的问题.

Now, StackOverflow has several topics about this error message, so I've already got the solution (in my case I'll have to use triggers), but I'm curious as to why there is such a problem at all.

据我了解，他们基本上想要避免两种情况 - 循环和多条路径.循环将是两个表具有彼此级联的外键.好的，一个循环也可以跨越多个表，但这是基本情况，更容易分析.

As I understand it, there are basically two scenarios that they want to avoid - a cycle and multiple paths. A cycle would be where two tables have cascading foreign keys to each other. OK, a cycle can span several tables too, but this is the basic case and will be easier to analyze.

当 TableA 具有 TableB 和 TableC 的外键，而 TableB 也具有 TableC 的外键时，就会出现多条路径.再一次 - 这是最低限度的基本情况.

Multiple paths would be when TableA has foreign keys to TableB and TableC, and TableB also has a foreign key to TableC. Again - this is the minimum basic case.

我看不到在任何这些表中删除或更新记录时会出现任何问题.当然，您可能需要多次查询同一个表以查看哪些记录需要更新/删除，但这真的有问题吗?这是性能问题吗?

I cannot see any problems that would arise when a record would get deleted or updated in any of those tables. Sure, you might need to query the same table multiple times to see which records need updating/deleting, but is that really a problem? Is this a performance issue?

在其他 SO 主题中，人们甚至将使用级联标记为有风险" 并声明 "解决级联路径是一个复杂的问题".为什么?风险在哪里?问题出在哪里?

In other SO topics people go as far as to label using cascades as "risky" and state that "resolving cascade paths is a complex problem". Why? Where is the risk? Where is the problem?

推荐答案

您有一个子表，其中包含来自同一父级的 2 个级联路径:一个删除"，一个空".

You have a child table with 2 cascade paths from the same parent: one "delete", one "null".

什么优先?之后你期待什么?等等

What takes precedence? What do you expect afterwards? etc

注意:触发器是代码，可以为级联添加一些智能或条件.

Note: A trigger is code and can add some intelligence or conditions to a cascade.

这篇关于外键级联多个路径和循环有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！