同一张表的2个外键可能会导致循环或多个级联路径 [英] 2 foreign key to the same table may cause cycles or multiple cascade paths

问题描述

圣诞快乐，

我正在寻找一种替代的数据库设计，以避免这种ULTRA SAFE错误:

I'm looking to find a alternative database design to avoid this ULTRA SAFE error:

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

当同一表的2个字段引用相同的FK时.例如:

When 2 fields of the same table refer to the same FK. Eg:

People: {Id, Name, Age, Gender}

FamilyRelative: {PersonId, FamiliarId, Relationship}
            FK: FamilyRelative.PersonId ->People.Id ON DELETE CASCADE ON UPDATE CASCADE
            FK: FamilyRelative.FamiliarId->People.Id ON DELETE CASCADE ON UPDATE CASCADE

在第二个FK上引发错误.

Throws an error on the second FK.

PS:我正在SQL Server 2008R2中对其进行测试

PS: I'm testing it in SQL Server 2008R2

推荐答案

您可以在 INSTEAD OF 触发器中进行处理，因此当您尝试从 dbo.People 中删除时>，您可以先在 dbo.FamilyRelation 上执行必要的删除操作，以避免出现任何完整性错误:

You can handle this in an INSTEAD OF trigger, so when you try and delete from dbo.People, you can perform the necessary delete on dbo.FamilyRelation first to avoid any integrity errors:

CREATE TRIGGER dbo.People_Delete ON dbo.People
INSTEAD OF DELETE
AS
BEGIN

    -- PERFORM THE DELETES ON FAMILY RELATIVE
    DELETE  dbo.FamilyRelative
    FROM    dbo.FamilyRelative fr
            INNER JOIN deleted d
                ON d.ID IN (fr.PersonID, fr.FamiliarID);

    -- PERFORM THE DELETES ON PEOPLE
    DELETE  dbo.People
    WHERE   ID IN (SELECT d.ID FROM deleted d);

END
GO

关于SQL Fiddle的示例

Example on SQL Fiddle

我不知道您将如何处理 ON UPDATE CASCADE ，因为当您更新主键时，您将丢失触发器中已插入和已删除表之间的链接.

I don't know how you would handle ON UPDATE CASCADE since when you update the primary key you lose the link between the inserted and deleted tables within your trigger.

这篇关于同一张表的2个外键可能会导致循环或多个级联路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！