SQL Server Relation table parent->child and cycle cascade

Question

I have a sql table (let's call it Person) like, for example :

TABLE Person

Id  Firstname  Lastname

I want to make relations beetwen somme of this person (parent/child relation) so I built another table (let's call it Person_Relation) like that :

TABLE Person_Relation

Id_person_parent   Id_person_child

I made a constraint to avoid the case where parent = child (it would be awkward !) and now i try to make foreign key beetwen table Person and table Person_Relation.I am currently able to make one foreign key, but when i try to set the second I get a : may cause cycles or multiple cascade paths error.

Knowing that I would keep the 'Delete cascade' to automatically delete links in Person_Relation table when an entry in Person is deleted, is there any clean solution to do that ?

Thank you in advance.

score 2 · Accepted Answer · edited May 23 '17 at 12:28

SQL Server won't let you create multiple cascade paths that could theoretically conflict. For more on that, see this answer.

One way to still achieve your goal is to use a trigger in place of a foreign key with a cascade action.

CREATE TRIGGER dbo.Person_Delete
ON dbo.Person
FOR DELETE
AS
  DELETE dbo.Person_Relation 
  WHERE Id_person_parent IN (SELECT Id FROM deleted)
    OR Id_person_child IN (SELECT Id FROM deleted);

SQL Server Relation table parent->child and cycle cascade

1 Answers1