March 9, 2009 at 7:10 am
I have table
A(id int identity(1,1) PRIMARY KEY)
and table
B(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
March 10, 2009 at 8:50 am
hi
i think it is better to use a trigger for this goal
March 10, 2009 at 10:58 am
If I understand correctly, Table B has a composite key of Id1 and Id2, each being a value in one or more of Table A’s primary key.
Do you have the authority to change the primary key of Table B to an identity key, and create a unique index for Table B consisting of Id1 and Id2? This way, the primary key of Table b does is not a foreign key into Table A.
March 10, 2009 at 11:04 am
pss (3/10/2009)
If I understand correctly, Table B has a composite key of Id1 and Id2, each being a value in one or more of Table A’s primary key.Do you have the authority to change the primary key of Table B to an identity key, and create a unique index for Table B consisting of Id1 and Id2? This way, the primary key of Table b does is not a foreign key into Table A.
Don't do this. No need. Keep the composite PK.
Just use a trigger for the referential integrity control, since a constraint can't do what you need. Triggers work just fine for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2009 at 5:37 am
i used a trigger and it worked.
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply