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 |
|
kamii47
Constraint Violating Yak Guru
353 Posts |
 Posted - 2008-01-24 : 11:30:17
|
| I'm having some DB-design related problem:I am trying to define two foreign keys that reference to same column in the same parent table. I have a following tables:Contact(ContactID* , Name)Department(DeptID* ,DeptName, HeadID(fk), ManagerID(fk) )Now I need to apply Cascade Update for both HeadID & ManagerID.SQL Server does not allow both of them.Error says that it will create cycles or multiple cascade paths.How do I resolve this? Or is there any other way to achieve the required featureKamran ShahidSr. Software Engineer(MCSD.Net)www.netprosys.com |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-24 : 14:21:20
|
| There may be some ways to achieve that requirement but I would first ask under what circumstances would you want to update the primary key of your contact table? Generally speaking that is not a good thing to do.But I'm pretty sure sql server won't allow multiple cascade paths so that would leave uglier, manual update cascades (maybe via a triger). But again, I would remove the ability to update the master PK value. Perhaps force the user to create a new contact, reassign the child values then delete the old contact.Be One with the OptimizerTG |
 |
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2008-01-26 : 02:37:28
|
| Thank you very much for your help! I totally agree with your views but the problem that I shouldn't allow customer ID to be modified. But the problem is that I'm maintaining an already running application, and can't change the design so easily.Just to simplify the problem I created the contact-department scenario in order to get answer to my multiple cascading path problem.Well the actual problem is that I'm maintaining Vehicle Records that include various parameters like, maker, category, color etc. in order to make the schema dynamic, instead of making separate tables for each of the parameter, a single parameter table is created with param_code, parent_child, parent_code, description.columns (param_code parent_child parent_code) are defined as composite key for the parameter table.param_code parent_child parent_code description---------- ---------- ---------- --------------------maker P null Parameter-Makercategory P null Parameter-Categorycolor P null Parameter-ColorTY C maker ToyotaHN C maker HondaSZ C maker SuzukiSD C category SedanHB C category HatchbackVN C category VanSLV C color SilverRED C color RedWHT C color WhiteBLK C color BlackNow the vehicle table has parameter columns that store just the param_code value. like auto_maker=TY, auto_color=BLK etc.If the code of any parameter record is updated how can I cascade it to the vehicle tableKamran ShahidSr. Software Engineer(MCSD.Net)www.netprosys.com |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-26 : 08:10:17
|
| There are a few details I'm still not clear on. Could you please post a script to create these 2 tables and populate them with a few rows of sample data? Include an UPDATE statement to the parameter table that you would want to cascade to itself and the vehicle table. Even though the actual situation is more complex then your original scenario, I still don't see why you would update a parameter code. I understand that you "inherited" an existing system though we'll see what we can do when you post those DDL and DML statements...Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
err it will create cycles or multiple cascade path