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 |
|
waldis
Starting Member
21 Posts |
 Posted - 2007-03-10 : 05:00:01
|
I'm having a little difficulty here and hope someone will be able to help me.I have four tables, the first table (T1) is connected to the fourth table (T4) directly or through the second (T2) and third (T3) tables. The T4 has a key fields from the T3 and T1 and when I try to set up the foreign keys (T1.t1_id=T4.t1_id and T3.t3_id=T4.t3_id) it wouldn't let me to execute the second FK, saying "Introducing FOREIGN KEY constraint 'FK_T1_SSI_T4' on table 'SSI_T4' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.", but if I change that it wont work the way I need it to. What I need is whenever the record fron T1 is deleted, all the related records from rest of the tables should be deleted as well (e.g. if I enter id 1 in T1 and then I add some records by going to T4 and some records by going to T2, T3, and then T4, whenever I delete id 1 from T1 I need it to delete all related records from T2 -> T3 -> T4 as well as find and delete all related records by t1_id) How should I proceed?![]() It won't let me to attach an image :(Thank you,Waldis |
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-03-10 : 05:14:44
|
| Waldis,can u post the table structure? coz it a little bit confusing. with table structure, we can get the prob.Mahesh |
 |
|
|
waldis
Starting Member
21 Posts |
Posted - 2007-03-10 : 13:19:28
|
| Here is the link to the image of what the structure looks like:[url]www.waldis.org/diagram.jpg[/url]Thanks,Waldis |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-03-12 : 07:50:52
|
| Hi ya.Does T1 need to be linked directly to T4? "A table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree. To work around this problem, do not create a foreign key that will create more than one path to a table in a list of cascading referential actions."Drew |
|
|
|
waldis
Starting Member
21 Posts |
Posted - 2007-03-12 : 14:44:00
|
| It's even worse, now I need to add two more tables T5 and T6, where T5 should be connected to T1, T6 to T5, and T4 to T6. In other words T1 would have three different paths how to get to T4. I wanted to avoid creating three identical tables (triplicating T4) by just adding three different key fields in it and linking them to appropriate tables. In reality T4 is a huge table and to code and keep up with three different sets of web forms, which are basically identical, looks to me a waste of time and space, but if I can't find a solution I will have to go that way... :(Thanks,Waldis |
|
|
|
waldis
Starting Member
21 Posts |
Posted - 2007-03-12 : 14:48:45
|
| One more thing to clarify, those records in T4, coming three different paths, are not related to each other, there is one separate set of records for each path {T1->T2->T3->T4; T1->T4; T1->T5->T6->T4}, it just seems to be appropriate to combine then into one table since the fields are identical.Thanks,Waldis |
|
|
|
|
|
|
|
|
Foreign keys