| Author |
Topic |
|
BoroDrummer
Starting Member
9 Posts |
 Posted - 2011-04-01 : 08:04:08
|
I have the following tables specified:Table1------Table1_IDcolumn2...Table2------Table2_IDcolumn2...Table3------Table3_IDTable2_ID (FK Table2->Table2_ID)Table1_ID (FK Table1->Table1_ID) Table1 and Table2 are populated first. Table3 can only hold existing IDs in Table1_ID and Table2_ID (hence the foreign keys).However, I want to cascade delete records in Table3 when related records are deleted in Table1 and Table2.If I set the Delete Rule to Cascade on the FK relationships they get set backwards. I.E. they cascade from Table3 to Table1 and Table2.How do I get around this? I can't set the foreign keys the other way round as the records always exist in Table1 and Table2 but not necessarily in Table3...Sorry if that was confusing! |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-04-01 : 10:05:38
|
| DELETE FROM Table3WHERE Table1_ID = @IDDELETE FROM Table1WHERE Table1_ID = @IDThis would basically go under the assumption that if you are deleting a single row from Table1, that any foreign keys pointing back to that row will have to go too. So there might be multiple records removed from Table3 for the 1 row in Table1.Hey, it compiles. |
 |
|
|
BoroDrummer
Starting Member
9 Posts |
Posted - 2011-04-01 : 10:10:36
|
| Yes that's what I want, but is there a way to set this to execute automatically within SQL Server? Like the Cascade Delete option?Thanks for the help. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-01 : 10:22:21
|
| You can't cascade the delete in that direction (in fact it makes no sense). To do it "automaticaly" the only way I know how to do that is to add a trigger or two. |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-04-01 : 10:51:50
|
| The way I handle it "automatically" is via stored procedure. The stored proc takes in the ID of the row in Table1 that I want to delete, but before I do that, I run through all the tables that have foreign keys pointing back to Table1 and do my deletes there. My final delete statement is on Table1. It is a pain and gets quite cumbersome if you have a table with hundreds of foreign keys, but that is the best way I have found to handle it. Now, if you are open to the idea there is another option. In the past I have added a column to Table1 called "intDelete". This is a 0/1 value column that defaults to 0. If the record is set to 1, then that means that record is "deleted". The stored procs that hit that table are coded with "WHERE Table1.intDelete = 0" so that only records with 0 in the intDelete column are returned. To the user it appears that the record has been deleted, but in fact it is still there just hidden.Hey, it compiles. |
|
|
|
BoroDrummer
Starting Member
9 Posts |
Posted - 2011-04-01 : 12:15:36
|
| Ok, great!I'm already using stored procedures but I'm trying to make the code as streamlined as possible. I'll just add lines to delete the offending tables manually.Thanks a lot, you've been a big help! |
|
|
|
BoroDrummer
Starting Member
9 Posts |
Posted - 2011-04-06 : 08:16:17
|
Ok, I've solved the above problem using stored procedures to delete Table3 records when Table1 and Table2 records are deleted......but I keep coming up against this problem in other tables in my project. I've noticed that if I simplify the table structure and cascade delete on only 1 foreign key everything is fine. I.e.:Table1------Table1_IDcolumn2...Table2------Table2_IDcolumn2...Table3------Table3_IDTable2_IDTable1_ID (FK Table1->Table1_ID) Multiple records in Table3 are deleted when matching Table1_IDs are deleted from Table1. Or...Table1------Table1_IDcolumn2...Table2------Table2_IDcolumn2...Table3------Table3_IDTable2_ID(FK Table2->Table2_ID)Table1_ID Multiple records in Table3 are deleted when matching Table2_IDs are deleted from Table2.However, when I try to introduce the cascade delete on both foreign keys, I get a SQL Server error: "Foreignkey constraint may cause cycles or multiple cascade paths".I don't understand why this is the case? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-06 : 11:02:22
|
| This may not be enought of en explianation, but basically you could delete a row in Table1. That then deleted the row in Table3. Which could cause a row in Table 2 to get deleted. But that ID is referened by Table 3, so a row in table 3 gets deleted, but that refereences a different ID in Table 1, so that needs to be deleted, but other rows in table 3 have that same ID so they neeed to be deleted.. and I've gone cross eyed. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-04-06 : 11:06:10
|
| Can I ask why you want to do this? Generally, if you are dealing with cascade delete is this fashion there is an issue or it's just part of dealing with data deprecation. What your specifics are, I've no idea. But, if you have a domain table you don't what to delete the value out of the domain table when one reference stops pointing to it. Rather, it is referenced by other rows or it's a domain value and just sits there waiting to be referenced. |
|
|
|
BoroDrummer
Starting Member
9 Posts |
Posted - 2011-04-07 : 03:49:18
|
quote:
Originally posted by Lamprey
This may not be enought of en explianation, but basically you could delete a row in Table1. That then deleted the row in Table3. Which could cause a row in Table 2 to get deleted...
That last sentence is where I disagree with you. Deleting a record from Table3 does not cascade delete rows in Table1 or Table2. The cascade delete only works in the opposite direction (primary key to foreign key). So there is no logical explanation as to why this is not allowed.But that's the crux of the matter: it's not allowed. So I'll just find another way.Thanks for the help. |
|
|
|
|
Problem with cascade delete