July 27, 2004 at 12:16 pm
I'm trying to create two FK relationships between the same two tables and enforce cascade R.I. with both relationships.
tblFOO.field1 <=> tblBAR.field1 and
tblFOO.field2 <=> tblBAR.field1
I can add one relationship (either) with cascade options set with no problem.
Get this error when trying to add the 2nd one:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_tblFOO_tblBAR' on table 'tblFOO' 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.
I can add the relationship, just not with cascade update/delete options, which I need. i.e. When a row is deleted from tblBAR, I want it to do a corresponding delete from tblFOO on the occurrence of the key value in EITHER field1 or field2.
How do I accomplish this?
thx
July 27, 2004 at 2:46 pm
You can write a trigger to accomplish this. You cannot cascade to two different columns.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply