I may have misinterpreted the error I just got...but.. is it impossible in SQL Server to have a cascade delete FK constraint that leads to a table which has another cascade delete FK?? What's a workaround? Triggers?
I have a table cp_campaigns linked to cp_kits which is linked to cp_kit_items. (they're linked as you'd expect.. cp_kits has a campaign_id column that's a FK to the PK in cp_campaigns, cp_kit_items has a FK kit_id which points back to cp_kits).
Now, in the relationship between cp_kits and cp_kit_items, I have a cascade delete.. so if you delete a kit, the kit items are also wiped out.
But when I try to set a cascade delete between cp_campaigns and cp_kits (delete all kits when the corresponding campaign is deleted), I get this error (upon trying to save the diagram):
'cp_campaigns' table saved successfully
- Unable to create relationship 'FK_cp_kits_cp_campaigns'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_cp_kits_cp_campaigns' on table 'cp_kits' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
Now here's the really weird part. I have a cp_elements table (which contains a campaign_id FK linked back to cp_campaigns), and a cp_orderables table which links back to cp_elements. Pretty much the same kind of relationship as the kits above, right? Well, VERY oddly, I am able to do cascade deletes on BOTH the relationship between campaigns & elements, AND elements & orderables!
What the heck is going on? Is the error above caused by something else?? SQL errors are so cryptic sometimes...
I suspect that you have a relationships established between these table that you are not aware of. You can get this error if a relationship exists between cp_campaigns and cp_kit_items, or if two of your tables are involved in a cascading relationship with another table. (Sounds perverted, doesn't it?) Try scripting out the three tables and see what shows up.
If it's not practically useful, then it's practically useless.