SQL Server外键原因周期或多个级联路径 [英] SQL Server Foreign Key cause cycles or multiple cascade paths

问题描述

我在将级联删除添加到SQL Server的外键时遇到问题.表A具有三列.表A中的第1列和第2列是表B中同一列的外键查找.我希望删除表B中的一行，以便基于这些外键在表A中的行上级联删除.

I'm having problems adding a cascade delete onto a foreign key in SQL Server. Table A has three columns. Column 1 and 2 in Table A are foreign key look ups to the same column in Table B. I want a delete of a row in Table B to cascade a delete on a row on Table A based on these foreign keys.

表A中的另一列具有对表C的外键查找.如果表C中的行被删除，那么我希望表A中的对应单元格设置为null.

The other column in Table A has a foreign key lookup to table C. If a row in table C is deleted then I want the corresponding cell to be set to null in Table A.

当我添加这些约束时，会抛出错误:

When I add in these constraints I am thrown the error:

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

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

对此我有些困惑，Oracle似乎对这种逻辑非常满意.我正在使用Liquibase添加这些约束.我认为该错误取决于我的逻辑而不是语法，但为完整起见，这里是管理外键的Liquidbase脚本:

I am a little stuck with this, Oracle seems perfectly happy with this logic. I am adding in these constraints using Liquibase. I think the error is down to my logic and not syntax but for completeness here is the liquidbase script that manages the foreign keys:

    <addForeignKeyConstraint constraintName="FK_RDU_FROMDELIVERABLEUNITREF" baseTableName="relatedDeliverableUnit"
                             baseColumnNames="FROMDELIVERABLEUNITREF" referencedTableName="DELIVERABLEUNIT" referencedColumnNames="DELIVERABLEUNITREF" onDelete="CASCADE"/>

    <addForeignKeyConstraint constraintName="FK_RDU_TODELIVERABLEUNITREF" baseTableName="relatedDeliverableUnit"
                             baseColumnNames="TODELIVERABLEUNITREF" referencedTableName="DELIVERABLEUNIT" referencedColumnNames="DELIVERABLEUNITREF" onDelete="CASCADE"/>                        

    <addForeignKeyConstraint constraintName="FK_RDU_RELATIONSHIPREF"
                             baseTableName="relatedDeliverableUnit" baseColumnNames="RELATIONSHIPREF" referencedTableName="RELATIONSHIPTYPES" referencedColumnNames="RELATIONSHIPREF" onDelete="SET NULL"/>                      

在此先感谢您的帮助

推荐答案

我找不到更高版本的相应文档，但是

I can't find corresponding documentation for later versions, but the SQL Server 2000 BOL addresses this issue:

由单个DELETE或UPDATE触发的一系列级联引用动作必须形成不包含循环引用的树.在由DELETE或UPDATE导致的所有级联引用动作的列表中，任何表都不能出现多次.级联引用动作的树到任何给定表的路径不得超过一条.当该树的任何分支遇到一个表(未指定操作或为默认操作)时，该分支将终止.

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default.

以后的版本都没有改变.您对此感到犯规:

And later versions haven't changed this. You're falling foul of this:

级联引用动作树不得具有指向任何给定表的多个路径

The tree of cascading referential actions must not have more than one path to any given table

我知道要完成此操作的唯一方法是使用INSTEAD OF触发器而不是使用ON DELETE...来实现B和A之间的级联之一.

The only way I know of to accomplish this is to implement one of the cascades between B and A using an INSTEAD OF trigger, rather than using ON DELETE....

表A和C之间的关系不应该受到任何影响.

The relation between tables A and C shouldn't be impacted by any of this.

( 2008 BOL )

这篇关于SQL Server外键原因周期或多个级联路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！