2 foreign key to the same table may cause cycles or multiple cascade paths
Merry Christmas,
I'm looking to find a alternative database design to avoid this ULTRA SAFE error:
Introducing FOREIGN KEY constraint '%1' on table '%2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
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
Throws an error on the second FK.
PS: I'm testing it in SQL Server 2008R2
You could remove the DELETE CASCADE Action on the FamilyRelative.FamiliarId
And then when you want to delete a People record first you
DELETE FROM FamilyRelative
WHERE FamiliarId = @PeopleId
and after that you
DELETE FROM People
WHERE Id = @PeopleId
The last delete will take care of the remaining FamilyRelative records with FamilyRelative.PeopleId = @PeopleId using the CASCADE rule
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
Example on SQL Fiddle
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.
上一篇: 但外键不存在