2 foreign keys to the same table can cause loops or several cascading paths

Question

Merry Christmas,

I am looking to find an alternative database design to avoid this ULTRA SAFE error:

Representing the FOREIGN KEY constraint "% 1" in table "% 2" can cause loops or multiple cascading paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION or change other FOREIGN KEY constraints.

If 2 fields of the same table belong to the same FK. For example:

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 in the second FK.

PS: I am testing it in SQL Server 2008R2

Answer 1

DELETE CASCADE FamilyRelative.FamiliarId

, People,

DELETE FROM FamilyRelative 
WHERE FamiliarId = @PeopleId

DELETE FROM People
WHERE Id = @PeopleId

FamilyRelative FamilyRelative.PeopleId = @PeopleId CASCADE

Answer 2

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, .