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 |
|
laailalalaa
Yak Posting Veteran
57 Posts |
 Posted - 2009-03-09 : 09:09:50
|
| I have table A(id int identity(1,1) PRIMARY KEY) and tableB(id1 int, id2 int, primary key(id1, id2))I want to set id1 and id2 as foreign keys that would both reference id in table A.When i do this i cannot enforce Cascade on Delete and Update actions on one of the Foreing Key constraints. I receive the following message:Unable to create relationship 'FK_B_A1'. Introducing FOREIGN KEY constraint 'FK_B_A1' on table 'B' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint.I need to preserve these tables with their structures and, obviously, when i delete a record from A with id = 1, for instance, all records in B that have either id1 = 1 or id2 = 1 need to be deleted.Any ideas on how to proceed?Thank you |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-09 : 09:53:27
|
| (at least as of sqlserver 2000) Sql doesn't allow cascading deletes for this scenario. It can't tell if a circular reference would occur. You will need to implement the cascade delete logic using a trigger. I have to assume based on that error that this hasn't changed in 2005.Be One with the OptimizerTG |
 |
|
|
laailalalaa
Yak Posting Veteran
57 Posts |
Posted - 2009-03-14 : 07:36:03
|
| it worked with a trigger, thank u |
|
|
|
|
|
|
foreingn keys referencing same primary key