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 |
shaihan
Starting Member
20 Posts |
Posted - 2007-04-08 : 15:40:18
|
i want to reffer A1,A2 column from a Table A to B1 column of Table B. when i use the on update cascade option for the columns A1 & A2 i get an error for the 2nd one. sql:alter table Aadd constraint fk_key1 foreign key (a1) references b(b1) on update cascade;alter table Aadd constraint fk_key2 foreign key (a2) references b(b1) on update cascade;after executing the sql i get the following error for the 2nd sql:Introducing FOREIGN KEY constraint 'FK_key2' on table 'A' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.if i dont use cascade option then i dont get any error.but i need to synchronize the data. now can anyone help me how to add the cascade option working for the 2nd column of Table A without seperating the column in a different table?thnxS ]-[ /-\ | ]-[ /-\ N |
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-04-08 : 17:34:57
|
It does seem strange that you need columns b1 and b2 in the B table (since they are the same)It might be an effect of your simplistic example.But the error you get can only be circumvented by altering the data model or introducing triggers to enforce the constraint.rockmoose |
|
|
shaihan
Starting Member
20 Posts |
Posted - 2007-04-09 : 13:43:49
|
thanks for ur suggestion rockmoose. well i know both columns are same but they may have different value for each row representing different meaning. i thought about writing triggers to update the columns but i wanted to do it using the cascade option. i guess i dont have any other choice now. again thnx for ur help.S ]-[ /-\ | ]-[ /-\ N |
|
|
|
|
|