Handling database record deletions with several levels of offspring

Question

We've got a database that generally has this structure:

Master Record Table
id (pk)
MasterRecordId <-- constrained to be unique

Children/Siblings (2nd Generation, if you will):

Table1
(  table1ID (pk),
  MasterRecordID (fk))

Table2
(  Table2ID(pk),
  MasterRecordID (fk))

Grandkids (3rd Generation):

Table3
( Table3ID (pk)
 Table1ID (fk))

Table4
 (Table4ID (pk)
  Table1ID (fk))

Table5
 (Table5ID (pk)
  Table2ID (fk))

Not every table in the second generation has kids. There is restricted delete functionality in the app (you can delete any individual record, but FKs will prevent the deletion in many cases; the delete functionality is borked and does not fail gracefully to be blunt).

I have been tasked with investigating the best way to handle deletion. For the purging of an entire record from the Master down to the grand kids, the back end is the only way to do this. That made The Powers That Be happy. But, you know, Users Lie, and so it turns out we may need to change this (both so I don't have to serve as the occasional Official Record Deleter and because there are certain types of Gen 2 records users delete frequently.

Cascading Deletes were the first option, because TPTB would prefer this not require work on a new build of the app. And because it's what popped out of my bosses' mouth at the end of that particular meeting. My Gen 2 -> Gen 3 cascades are all working fine (and this covers the most frequent Use Case/Story/What Have You). I then updated all the Master -> Gen 2 Foreign Keys to cascade on Delete. In hoped this would allow a deletion of the master record and that all other kids and grand kids would go with it. No good; I get an error message violating the first Master -> Gen 2 FK to come up when I try to delete the master record. I've double checked; FKs are set to cascade on delete.

What am I not understanding about cascading deletes with more than 1 level of table relationships? I'm reading as much as I can (as time permits) but I haven't yet discovered the knowledge that will lead me out of this dark time. Is cascading the wrong approach?

Secondly, there are two other options as I see it:

1. Do all deletion in the app. Not preferred, but if it's the only option it's the only option. I know there are arguments that it's the best option, but TPTB have different views of best than I do (and while they're all batshit crazy, they sign the checks).

2. Handle deletes via trigger? I'm unclear if Foreign Keys will get inthe way of this, but it occured to me this might be an option.

Well, also:

1. Do the Gen 2 -> Gen 3 cascading. And then the few people with delete permissions will just have to follow the rituals to do full deleting (that would be: delete all Gen 2 records individually, then delete the master). Or, I'll be stuck as Official Record Deleter.

Answer 1

All the T-SQL code you'd write to "manually" delete children, grandchildren, et. al. could be loaded into a trigger on the MasterRecord table, but I'd consider that to be a horrible solution, if for no other reason than it severely obfuscates critical database functionality.

I'd do it within the app (or, preferrably, within a stored procedure). But if that's not an option, then yes, it looks like you're stuck with triggers or cascading deletes (and maybe a combination of both). Can you lie to TPTB? Does the "what they don't know won't hurt you" rule apply?

I've never done cascading deleted, and gods willing I never will. I look forward to reading someone else's post that answers your question.

Answer 2

So, apparently I did have something wrong with one or more of my relationships (deleting on the back end made it clearer). I put up a fresh copy of the DB and reapplied my scripts and now everything is working. This article at MSDN helped me realize the problem had to be one of my relationships. And was helpful in general.

So we'll see what TPTB decide I should do next.