Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths.

_{Friday, March 9, 2018 4:38 PM}

Hello,

I'm encountering an error when trying to deploy my database. The error says:

"Introducing FOREIGN KEY constraint 'FK_ProjectRecommendation_Project' on table 'ProjectRecommendation' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

I'm encountering this error for three tables:

* ProjectRecommendations
* ProjectSafeguards
* ProjectDeviations

Each of these three tables have foreign key references to Project. I'm trying to set these three tables to delete any records with references to a project when that project is deleted. For example:

CONSTRAINT [FK_ProjectRecommendation_Project] FOREIGN KEY ([ProjectId]) REFERENCES [Project]([ProjectId]) ON DELETE CASCADE

My google research tells me that this error arises when you have multiple cascading paths. And I do. Notice in the graph above that you have a chain of relations starting from Project going to Nodes, then to Deviations, then to Causes, then to Consequences, then branching off to Recommendations and Safeguards. These are each 1-to-many relations (all in the same direction) and they are each configured to cascade upon deletion of their parent (the 1 side). But as you can see, Recommendations and Safeguards (the items at the end of the line along this path) have a 1-to-many relation to ProjectRecommendations and ProjectSafeguards. You also see the same relation between Deviations and ProjectDeviations. So this path forms a circle. Between Recommendations and ProjectRecommendations, I have it set to cascade on delete (when a ProjectRecommendation gets deleted, delete all Recommendations). Same for the relation between Safeguards and ProjectSafeguards, and also for Deviations and ProjectDeviations.

What I don't understand is why this is such a terrible thing. If a project gets deleted, I would like everything along the path starting with Nodes to be deleted, and also everything along the other three paths (ProjectDeviations, ProjectRecommendations, and ProjectSafeguards) to be deleted. Everything is set to cascade on delete, so I don't see why this would be an issue. No conflicts will arise, no foreign keys will be orphaned. I don't get it.

_{Friday, March 9, 2018 5:25 PM}

Did you check this article at https://support.microsoft.com/en-gb/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-tha ?

Make sure there is no the second FK ON DELETE CASCADE in a specific table referenced by the same column Project.ProjectId.

A Fan of SSIS, SSRS and SSAS

_{Monday, March 12, 2018 5:38 PM}

You have multiple paths between 'Projects' other leaf tables like 'Consequences' and 'Recommendations'. This causes an ambiguity when a project is deleted and the engine tries to delete data from one path after another. To work around this issue ensure there is only one path in hierarchy or else implement your cascade delete through a trigger. Here is an article that tells you how: Solving the SQL Server Multiple Cascade Path Issue with a Trigger

HTH,
Cheers!!
Ashish
Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.