SQL 外键约束可能导致循环或多个级联路径？

Foreign key constraint may cause cycles or multiple cascade paths?

提问by onedaywhen

I have a problem when I try to add constraints to my tables. I get the error:

当我尝试向我的表添加约束时遇到问题。我收到错误：

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

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

My constraint is between a Codetable and an employeetable. The Codetable contains Id, Name, FriendlyName, Typeand a Value. The employeehas a number of fields that reference codes, so that there can be a reference for each type of code.

我的约束在一张Code桌子和一张employee桌子之间。该Code表包括Id，Name，FriendlyName，Type和Value。的employee具有多个字段的标号，从而可以存在对于每种类型的码的参考的。

I need for the fields to be set to null if the code that is referenced is deleted.

如果引用的代码被删除，我需要将字段设置为 null。

Any ideas how I can do this?

任何想法我怎么能做到这一点？

回答by onedaywhen

SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (CASCADE): you can and should still create the constraints without the referential actions. If you can't alter your design (or doing so would compromise things) then you should consider using triggers as a last resort.

SQL Server 对级联路径进行简单的计数，而不是尝试确定是否存在任何循环，而是假设最坏的情况并拒绝创建引用操作 (CASCADE)：您可以并且仍然应该创建没有引用操作的约束。如果你不能改变你的设计（或者这样做会妥协），那么你应该考虑使用触发器作为最后的手段。

FWIW resolving cascade paths is a complex problem. Other SQL products will simply ignore the problem and allow you to create cycles, in which case it will be a race to see which will overwrite the value last, probably to the ignorance of the designer (e.g. ACE/Jet does this). I understand some SQL products will attempt to resolve simple cases. Fact remains, SQL Server doesn't even try, plays it ultra safe by disallowing more than one path and at least it tells you so.

FWIW 解决级联路径是一个复杂的问题。其他 SQL 产品将简单地忽略该问题并允许您创建循环，在这种情况下，它将是一场竞赛，看哪个将最后覆盖该值，可能是设计者无知（例如 ACE/Jet 这样做）。我知道一些 SQL 产品会尝试解决简单的情况。事实仍然如此，SQL Server 甚至没有尝试，通过禁止多个路径来发挥它的超安全性，至少它告诉你。

Microsoft themselves advisesthe use of triggers instead of FK constraints.

Microsoft 自己建议使用触发器而不是 FK 约束。

回答by hans riesebos

A typical situation with multiple cascasing paths will be this: A master table with two details, let's say "Master" and "Detail1" and "Detail2". Both details are cascade delete. So far no problems. But what if both details have a one-to-many-relation with some other table (say "SomeOtherTable"). SomeOtherTable has a Detail1ID-column AND a Detail2ID-column.

具有多个级联路径的典型情况是：具有两个详细信息的主表，例如“Master”和“Detail1”和“Detail2”。两个细节都是级联删除。到目前为止没有问题。但是如果两个细节都与其他一些表（比如“SomeOtherTable”）有一对多的关系呢？SomeOtherTable 有一个 Detail1ID 列和一个 Detail2ID 列。

Master { ID, masterfields }

Detail1 { ID, MasterID, detail1fields }

Detail2 { ID, MasterID, detail2fields }

SomeOtherTable {ID, Detail1ID, Detail2ID, someothertablefields }

In other words: some of the records in SomeOtherTable are linked with Detail1-records and some of the records in SomeOtherTable are linked with Detail2 records. Even if it is guaranteed that SomeOtherTable-records never belong to both Details, it is now impossible to make SomeOhterTable's records cascade delete for both details, because there are multiple cascading paths from Master to SomeOtherTable (one via Detail1 and one via Detail2). Now you may already have understood this. Here is a possible solution:

换句话说：SomeOtherTable 中的一些记录与 Detail1-records 链接，SomeOtherTable 中的一些记录与 Detail2 记录链接。即使保证 SomeOtherTable-records 永远不会属于这两个 Details，现在也不可能让 SomeOhterTable 的记录级联删除这两个细节，因为从 Master 到 SomeOtherTable 有多个级联路径（一个通过 Detail1，一个通过 Detail2）。现在你可能已经明白了这一点。这是一个可能的解决方案：

Master { ID, masterfields }

DetailMain { ID, MasterID }

Detail1 { DetailMainID, detail1fields }

Detail2 { DetailMainID, detail2fields }

SomeOtherTable {ID, DetailMainID, someothertablefields }

All ID fields are key-fields and auto-increment. The crux lies in the DetailMainId fields of the Detail tables. These fields are both key and referential contraint. It is now possible to cascade delete everything by only deleting master-records. The downside is that for each detail1-record AND for each detail2 record, there must also be a DetailMain-record (which is actually created first to get the correct and unique id).

所有 ID 字段都是关键字段和自动增量。关键在于 Detail 表的 DetailMainId 字段。这些字段既是键约束又是引用约束。现在可以通过仅删除主记录来级联删除所有内容。不利的一面是，对于每个 detail1-record 和每个 detail2 记录，还必须有一个 DetailMain-record（实际上首先创建它以获得正确且唯一的 id）。

回答by Bill Cohagan

I would point out that (functionally) there's a BIG difference between cycles and/or multiple paths in the SCHEMA and the DATA. While cycles and perhaps multipaths in the DATA could certainly complicated processing and cause performance problems (cost of "properly" handling), the cost of these characteristics in the schema should be close to zero.

我会指出（在功能上）SCHEMA 和DATA 中的循环和/或多条路径之间存在巨大差异。虽然 DATA 中的循环和多路径肯定会使处理复杂化并导致性能问题（“正确”处理的成本），但模式中这些特征的成本应该接近于零。

Since most apparent cycles in RDBs occur in hierarchical structures (org chart, part, subpart, etc.) it is unfortunate that SQL Server assumes the worst; i.e., schema cycle == data cycle. In fact, if you're using RI constraints you can't actually build a cycle in the data!

由于 RDB 中最明显的循环发生在层次结构（组织结构图、部分、子部分等）中，不幸的是 SQL Server 假设了最坏的情况；即，模式循环 == 数据循环。事实上，如果您使用 RI 约束，您实际上无法在数据中构建循环！

I suspect the multipath problem is similar; i.e., multiple paths in the schema don't necessarily imply multiple paths in the data, but I have less experience with the multipath problem.

我怀疑多路径问题是相似的；即，模式中的多条路径不一定意味着数据中的多条路径，但我对多路径问题的经验较少。

Of course if SQL Server didallow cycles it'd still be subject to a depth of 32, but that's probably adequate for most cases. (Too bad that's not a database setting however!)

当然，如果 SQL Server确实允许循环，它仍然会受到 32 的深度限制，但这对于大多数情况可能已经足够了。（可惜这不是数据库设置！）

"Instead of Delete" triggers don't work either. The second time a table is visited, the trigger is ignored. So, if you really want to simulate a cascade you'll have to use stored procedures in the presence of cycles. The Instead-of-Delete-Trigger would work for multipath cases however.

“而不是删除”触发器也不起作用。第二次访问表时，触发器将被忽略。因此，如果您真的想模拟级联，则必须在存在循环的情况下使用存储过程。然而，替代删除触发器适用于多路径情况。

Celko suggests a "better" way to represent hierarchies that doesn't introduce cycles, but there are tradeoffs.

Celko 提出了一种“更好”的方式来表示不引入循环的层次结构，但需要进行权衡。

回答by Javier

There is an article available in which explains how to perform multiple deletion paths using triggers. Maybe this is useful for complex scenarios.

有一篇文章解释了如何使用触发器执行多个删除路径。也许这对于复杂的场景很有用。

http://www.mssqltips.com/sqlservertip/2733/solving-the-sql-server-multiple-cascade-path-issue-with-a-trigger/

http://www.mssqltips.com/sqlservertip/2733/solving-the-sql-server-multiple-cascade-path-issue-with-a-trigger/

回答by Eoin Campbell

By the sounds of it you have an OnDelete/OnUpdate action on one of your existing Foreign Keys, that will modify your codes table.

根据它的声音，您对现有外键之一进行了 OnDelete/OnUpdate 操作，这将修改您的代码表。

So by creating this Foreign Key, you'd be creating a cyclic problem,

所以通过创建这个外键，你会创建一个循环问题，

E.g. Updating Employees, causes Codes to changed by an On Update Action, causes Employees to be changed by an On Update Action... etc...

例如更新员工，导致代码被更新操作更改，导致员工被更新操作更改......等等......

If you post your Table Definitions for both tables, & your Foreign Key/constraint definitions we should be able to tell you where the problem is...

如果您发布两个表的表定义以及外键/约束定义，我们应该能够告诉您问题出在哪里......

回答by Rajnikant

This is because Emplyee might have Collection of other entity say Qualifications and Qualification might have some other collection Universities e.g.

这是因为 Emplyee 可能有其他实体的 Collection 说 Qualifications 和 Qualification 可能有一些其他的集合大学，例如

public class Employee{
public virtual ICollection<Qualification> Qualifications {get;set;}

}

}

public class Qualification{

public Employee Employee {get;set;}

public virtual ICollection<University> Universities {get;set;}

}

}

public class University{

public Qualification Qualification {get;set;}

}

}

On DataContext it could be like below

在 DataContext 上，它可能如下所示

protected override void OnModelCreating(DbModelBuilder modelBuilder){

modelBuilder.Entity<Qualification>().HasRequired(x=> x.Employee).WithMany(e => e.Qualifications);
modelBuilder.Entity<University>.HasRequired(x => x.Qualification).WithMany(e => e.Universities);

}

}

in this case there is chain from Employee to Qualification and From Qualification to Universities. So it was throwing same exception to me.

在这种情况下，存在从员工到资格和从资格到大学的链。所以它向我抛出了同样的异常。

It worked for me when I changed

当我改变时它对我有用

    modelBuilder.Entity<Qualification>().**HasRequired**(x=> x.Employee).WithMany(e => e.Qualifications); 

To

到

    modelBuilder.Entity<Qualification>().**HasOptional**(x=> x.Employee).WithMany(e => e.Qualifications);

回答by Tone ?koda

Trigger is solution for this problem:

触发器是这个问题的解决方案：

IF OBJECT_ID('dbo.fktest2', 'U') IS NOT NULL
    drop table fktest2
IF OBJECT_ID('dbo.fktest1', 'U') IS NOT NULL
    drop table fktest1
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'fkTest1Trigger' AND type = 'TR')
    DROP TRIGGER dbo.fkTest1Trigger
go
create table fktest1 (id int primary key, anQId int identity)
go  
    create table fktest2 (id1 int, id2 int, anQId int identity,
        FOREIGN KEY (id1) REFERENCES fktest1 (id)
            ON DELETE CASCADE
            ON UPDATE CASCADE/*,    
        FOREIGN KEY (id2) REFERENCES fktest1 (id) this causes compile error so we have to use triggers
            ON DELETE CASCADE
            ON UPDATE CASCADE*/ 
            )
go

CREATE TRIGGER fkTest1Trigger
ON fkTest1
AFTER INSERT, UPDATE, DELETE
AS
    if @@ROWCOUNT = 0
        return
    set nocount on

    -- This code is replacement for foreign key cascade (auto update of field in destination table when its referenced primary key in source table changes.
    -- Compiler complains only when you use multiple cascased. It throws this compile error:
    -- Rrigger Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, 
    -- or modify other FOREIGN KEY constraints.
    IF ((UPDATE (id) and exists(select 1 from fktest1 A join deleted B on B.anqid = A.anqid where B.id <> A.id)))
    begin       
        update fktest2 set id2 = i.id
            from deleted d
            join fktest2 on d.id = fktest2.id2
            join inserted i on i.anqid = d.anqid        
    end         
    if exists (select 1 from deleted)       
        DELETE one FROM fktest2 one LEFT JOIN fktest1 two ON two.id = one.id2 where two.id is null -- drop all from dest table which are not in source table
GO

insert into fktest1 (id) values (1)
insert into fktest1 (id) values (2)
insert into fktest1 (id) values (3)

insert into fktest2 (id1, id2) values (1,1)
insert into fktest2 (id1, id2) values (2,2)
insert into fktest2 (id1, id2) values (1,3)

select * from fktest1
select * from fktest2

update fktest1 set id=11 where id=1
update fktest1 set id=22 where id=2
update fktest1 set id=33 where id=3
delete from fktest1 where id > 22

select * from fktest1
select * from fktest2

回答by Amirhossein Mehrvarzi

This is an error of type database trigger policies. A trigger is code and can add some intelligences or conditions to a Cascade relation like Cascade Deletion.You may need to specialize the related tables options around this like Turning off CascadeOnDelete:

这是数据库触发器策略类型的错误。触发器是代码，可以向级联关系（如级联删除）添加一些智能或条件。您可能需要专门针对此相关的表选项，例如关闭 CascadeOnDelete：

protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
    modelBuilder.Entity<TableName>().HasMany(i => i.Member).WithRequired().WillCascadeOnDelete(false);
}

Or Turn off this feature completely:

或完全关闭此功能：

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

回答by user1477388

My solution to this problem encountered using ASP.NET Core 2.0 and EF Core 2.0 was to perform the following in order:

我在使用 ASP.NET Core 2.0 和 EF Core 2.0 时遇到的这个问题的解决方案是按顺序执行以下操作：

1. Run update-databasecommand in Package Management Console (PMC) to create the database (this results in the "Introducing FOREIGN KEY constraint ... may cause cycles or multiple cascade paths." error)

2. Run script-migration -Idempotentcommand in PMC to create a script that can be run regardless of the existing tables/constraints

3. Take the resulting script and find ON DELETE CASCADEand replace with ON DELETE NO ACTION

4. Execute the modified SQL against the database

1. update-database在包管理控制台 (PMC) 中运行命令以创建数据库（这会导致“引入 FOREIGN KEY 约束...可能导致循环或多个级联路径。”错误）

2. script-migration -Idempotent在 PMC 中运行命令以创建一个可以运行的脚本，无论现有表/约束如何

3. 获取生成的脚本并找到ON DELETE CASCADE并替换为ON DELETE NO ACTION

4. 对数据库执行修改后的 SQL

Now, your migrations should be up-to-date and the cascading deletes should not occur.

现在，您的迁移应该是最新的，并且不应发生级联删除。

Too bad I was not able to find any way to do this in Entity Framework Core 2.0.

太糟糕了，我无法在 Entity Framework Core 2.0 中找到任何方法来做到这一点。

Good luck!

祝你好运！