2 foreign key to the same table may cause cycles or multiple cascade paths

Question

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

What are you trying to achieve? When you delete a People record you want all FamilyRelative records with either Person.Id or FamiliarId = to that People.Id to be deleted? — Vland, Dec 24 '13 at 12:40
Possibly relevant:http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths and http://stackoverflow.com/questions/19910621/cascade-delete-in-many-to-many-self-reference-table — NoChance, Dec 24 '13 at 13:09
I suggest you look at the problem from a business view. The situation you have is the result of self referencing a Many-To-Many which is odd business wise. A person belongs to many families and a family has many persons is not detailed enough to be modeled. — NoChance, Dec 24 '13 at 13:18
@EmmadKareem if you prefer, we can use a example not so odd like **bill of materials** in a production environment as a list of components: `{FinishMaterialId, RawMaterialId, RawQuantity}`. Any ERP has one table like this! — Alex, Dec 24 '13 at 15:08
In theory (and maybe in some practices) you are correct, however, it is commonly required to capture the reason, type, and time of association between the two 'entities' rather that showing that they are related. — NoChance, Dec 24 '13 at 15:20

score 2 · Answer 1 · answered Dec 24 '13 at 12:48

2

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

answered Dec 24 '13 at 12:48

Vland

4,151
2
32
43

That's what I'm doing nowadays, but I wanted to know if there is any design alternative. – Alex Dec 24 '13 at 13:07
I think that the design is perfect. All persons/relatives are people, so they should stay in your People table. I'd stay with the good design and keep the references and deal with the problem in my delete queries and/or using a TRIGGER – Vland Dec 24 '13 at 13:18

GarethD · Answer 2 · 2013-12-24T13:24:09.183

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.

2 foreign key to the same table may cause cycles or multiple cascade paths

2 Answers2

Linked