April 6, 2010 at 10:59 am
Good morning
I work with sqlserver2005 but causes problems when trying to set relationship between tables. For this, i use Sql Server Management Studio.
For example.
I have 3 tables:
-'table1' with a field with name 'table1_field1'
-'table2' with a field with name 'table2_field1'
-'table3' with a field with name 'table3_field1'
Firstly, I set a relationship between table1 and table2 with the fields 'table1_field1' and 'table2_field1'
The table1 is the primary table and the delete rule is cascade. This work.
After, I set a relationship between table2 and table3 with the fields 'table2_field1' and 'table3_field1'
The table2 is the primary table and the delete rule is cascade. This work.
For last, I set a relationship between table1 and table3 with the fields 'table1_field1' and 'table3_field1'
The table1 is the primary table and the delete rule is cascade. This does not work.
Gives the following error:
Unable to create relationship 'FK_table3_table1'.
If you specify the FOREIGN KEY constraint 'FK_table3_table1' in the table table3 could occur in cycles or multiple paths waterfall. Specify ON DELETE NO ACTION o UPDATE NO ACTION or modify other restrictions FOREIGN KEY.
Why does this happen? I see no cycle
It´s a bug of sqlserver2005??
Thanks
April 6, 2010 at 11:03 am
In that case, if you delete from table1, it will delete from table2, which will then delete from table3. At the same time, you're telling it to delete from table3 directly from table1. Thus, a delete from table1 could have two different "paths" to delete from table3, which isn't something that SQL Server allows for directly.
If you really need table1 to directly delete from table3, and for table2 to directly delete from table3, you'll need to do that through triggers.
- 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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply