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

Question

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

junk.mail291276

SSCrazy

Points: 2503
More actions
March 9, 2018 at 9:43 am

#344341

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Sorry to burst your bubble, but that's just how CASCADE ON DELETE works. You really just can't have your cake and eat it too. The idea behind CASCADE ON DELETE was never to accommodate either a ring or a circle of dependency, but to only handle a pure hierarchy - at least in the sense that "pure" just means no linking back to the original from anywhere downstream of it. Because of the way that feature works, you just can't do what you want. You may be able to combine that with TRIGGERs on the tables that would otherwise cause you grief, but maybe you could also just not use FK relationships going back and instead rely on application logic to validate data and trigger logic to handle deletes for those tables causing pain?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

junk.mail291276 SSCrazy Points: 2503 More actions · Answer 2

All right, well guess I gotta bight the bullet. Thanks sgmunson.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

junk.mail291276 - Friday, March 9, 2018 10:43 AM
All right, well guess I gotta bight the bullet. Thanks sgmunson.

Creating hollow points, eh? <LOL> :hehe:

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)