Problem with cascading deletion (circular)
Hi, I am reletively new to DB programming and have come across a fairly poorly designed DB. I am trying to set up cascating deletes and triggers in such a way that if the application deletes an entry in Table A then all the associate data in table B through H will also be deleted. However because of SQL Server 2005 restrictions on Cascading deletes and the cycles in the involved tables I have not been able to do it. Does anyone have some tips or suggestions?
Here is the basic design:
Table A
Table B
Table C
Table D
Table E
Table F
Table G
Table H
Relationships
1) Table B --> Table A
2) Table C --> Table B
3) Table D --> Table B
4) Table E --> Table C
5) Table E --> Table D
6) Table F --> Table E
7) Table F --> Table G
8) Table H --> Table G
9) Table H --> Table B
I can get cascading deletes for relationships 1 to 4 but once I try 5 it
recognizes the B-C-D-E cycle.
Circular cascading deletes are not allowed.
You may be able to use triggers instead.
FROM The Books Online topic:
Cascading Referential Integrity Constraints
Multiple Cascading Actions
Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.
I think the Advanced Transact SQL Book by Tom Moreau and Itzik Ben-Gan has some design info around this.
