david_a_wallace (8/27/2012)
It doesn't seem to have the answer though that link.It says "The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default."
Setting the same primary key to cascade to two columns seems to make SQL 2005 think there is cicular reference but their is not?
I missed that you were trying to use 2 FKs on the same table. It seems that your design is flawed. Your data is denormalized in what is generally considered bad practice. You have setup a primary key that can change. This is very challenging to say the least. Do you have permission to change the tables? If so, this seems like a reasonable place to use an identity field as your primary key. Then your foreign keys reference the row and you can change the text of it and it will always be current.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/