2同一个表的外键可能导致周期或多个级联路径

圣诞节快乐，

我正在寻找替代数据库设计来避免这种ULTRA SAFE错误：

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

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

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上引发错误。

PS：我正在SQL Server 2008R2中测试它

---

您可以删除FamilyRelative.FamiliarId上的DELETE CASCADE操作

然后，当你想先删除人物记录

DELETE FROM FamilyRelative 
WHERE FamiliarId = @PeopleId

之后你

DELETE FROM People
WHERE Id = @PeopleId

最后一次删除将使用FamilyRelative.PeopleId = @PeopleId来处理剩余的FamilyRelative记录，使用CASCADE规则

---

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

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小提琴示例

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

链接地址: http://www.djcxy.com/p/65887.html

上一篇: 2 foreign key to the same table may cause cycles or multiple cascade paths

下一篇: One foreign key referenced to two primary keys of different tables?