2

Here's the scenario. - Parent Table: TEAMMEMBERS, with a primary key RecID - Child Table: TEAMMEMBERTASKS

I have two columns in the TEAMMEMBERTASKS table, ReportedBy and AssignedTo. Both of these columns use the RecID to store which team member reported a task and which team member the task is assigned to. The RecID could be the same for both columns, but that is not always the case.

I need to add in a FK for both child columns that check the relationship to the parent, and I would like to add ON UPDATE CASCADE to both of the foreign keys.

Whenever I try to do this, my second foreign key throws a 'may cause cycles or multiple cascade paths' error.

Here's my code:

ALTER TABLE [dbo].[TEAMMEMBERTASKS]  WITH CHECK ADD  CONSTRAINT 
[FK_AssignedTo_TeamMemberRecID] FOREIGN KEY([AssignedTo])
REFERENCES [dbo].[TEAMMEMBERS] ([RecID])
GO

ALTER TABLE [dbo].[TEAMMEMBERTASKS] CHECK CONSTRAINT 
[FK_AssignedTo_TeamMemberRecID]  
GO

ALTER TABLE [dbo].[TEAMMEMBERTASKS]  WITH CHECK ADD  CONSTRAINT 
[FK_ReportedBy_TeamMemberRecID] FOREIGN KEY([ReportedBy])
REFERENCES [dbo].[TEAMMEMBERS] ([RecID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[TEAMMEMBERTASKS] CHECK CONSTRAINT 
[FK_ReportedBy_TeamMemberRecID]
GO

With the current code, will this cause the RecID to be updated in both child columns or will it cause the update command to be restricted?

Should I just go ahead and write up a trigger that deals with this instead?

Jeremy Brown
  • 21
  • 1
  • What are you looking to accomplish with the `on update cascade`? – Dan Bracuk Jul 14 '17 at 12:22
  • 1
    Is there ever any need whatsoever to update `RecID`, the primary key of a table? If so, it may not be a good primary key. `ON UPDATE CASCADE` is much less useful than people think. (`ON DELETE CASCADE/NULLIFY` is another matter). – Jeroen Mostert Jul 14 '17 at 12:24
  • I might just be completely confused on what `on update cascade` does. What I want to make sure happens is if `RecID` in the parent is changed, that it is also changed in the child table. `RecID` should never change, but I wanted to make sure I have something in place that deals with the off chance that it will happen. – Jeremy Brown Jul 14 '17 at 12:36
  • 1
    The thing is, it will already be "dealt with" -- the update will simply be denied. Unless someone can make a business case for it, spending effort to make it possible is not only not worth it, but may enable scenarios that are undesirable to begin with. Is `RecID` an `IDENTITY` or other surrogate key? In this case, updating it is neither necessary nor desirable. Is it a business key, like a name-birth year combination? In that case, making updates possible may be worth it, but yes, you will need to write a trigger for it because declarative integrity is very conservative. – Jeroen Mostert Jul 14 '17 at 13:16
  • How is this not a duplicate? Have you googled your error message with ' "sql server" '? Eg https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – philipxy Jul 15 '17 at 18:50
  • @JeroenMostert Good points. For this specific case I would agree that since it is the surrogate key that should never be changed. – Jeremy Brown Jul 17 '17 at 13:45
  • @philipxy I understand the error. Could not find anything on updating, everything was about deleting – Jeremy Brown Jul 17 '17 at 13:46
  • Possible duplicate of [Foreign key constraint may cause cycles or multiple cascade paths?](https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) – philipxy Jul 17 '17 at 19:00
  • ?? The link I gave is not about a particular action, and explains that you cannot even *declare* two such cascading FKs, let alone get to DML, and that it doesn't make sense to do so, and the error message tells you what you have to do regardless of what you want to do or whatever else you do do. So all you are left with is triggers. You *think* you "would like to add ON UPDATE CASCADE to both of the foreign keys", but you are mistaken, and that link even explains the DBMS design issues around such declarations. PS The "deleting" is an *illustration* of why 2 such cascades *don't make sense*. – philipxy Jul 17 '17 at 19:09

0 Answers0