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

问题描述

在MSSQL 2005中，我刚刚触及臭名昭着的错误消息：

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

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

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

当TableA具有TableB的外键时， TableC和TableB也具有TableC的外键。再次 - 这是最基本的情况。

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

在其他SO主题中，人们甚至使用级联标签作为 risky 并指出解决级联路径是一个复杂的问题。为什么？风险在哪里？问题在哪里？

解决方案

您有一个包含来自同一个父级的两个级联路径的子表：一个delete，一个null。

什么优先？你以后期待什么？注意：触发器是代码，可以为级联添加一些情报或条件。

In MSSQL 2005 I just struck the infamous error message:

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.

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.

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?

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?

解决方案

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屋！