Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dev Null
Starting Member
39 Posts |
 Posted - 2005-12-28 : 19:17:34
|
| Hello all,I've got a table that defines some objects, and a linking table which defines relationships between those objects. Each entry in the linking table contains a bit of other information, and two foreign key references back to the object table - one for each of the two objects being linked.SQL Server wont let me make both of these foreign key references ON DELETE CASCADE, and I don't understand why. Whats wrong with defining a relationship which depends on both objects? If either object ceases to exist then the relationship likewise stops being relevant, and it makes sense for it to also be deleted. I could write a delete trigger on the object table of course, but it seems strange to have to re-invent something that already exists. Does anyone know if there is an option to turn off this restriction?For what its worth, the message I get when I try to create the second reference is:Msg 1785, Level 16, State 1, Server DEV-ROBERTM\PROGNOSIS, Line 144Introducing FOREIGN KEY constraint 'FK__device_wo__dest___1BC821DD' on table 'device_worst_calls' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Thanks for any advice, - rob. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-28 : 19:19:42
|
| SQL Server thinks it will get into an infinite loop when performing the cascade delete, so that's why it won't let you do it. There is no cascading workaround. We've had to resort to triggers and stored procedure code due to this.Tara Kizeraka tduggan |
 |
|
|
Dev Null
Starting Member
39 Posts |
Posted - 2005-12-29 : 02:03:53
|
| Thanks Tara.The trigger workaround wasn't exactly rocket science, so its not like it was much effort to fix, but its good to know there wasn't an easier way and the effort wasn't wasted... - rob. |
|
|
|
|
|
|
|
|
Multiple cascades