Is there any real advantage to managing cascading deletes in code rather than the DB?

Question

I'm going to set up this question with some pre-conditions that may make the question irrelevant, but here goes.

Assuming my database has a way to do cascading deletes and I'm not trying to code for the possible changing of DB, and my DB model is such that I always want a particular delete cascaded, is there any advantage to having to manage this cascading delete in application code rather than having the DB do it via DDL?

It seems to me that the extra code, the possibility of "missing it" there, and the (possibly zero) likelihood of missing out on the DB's built-in optimization of its own features are bigger downsides than any possible gains.

Am I missing anything?

Answer 1

The biggest benefit, to me in in the past, of DDL Cascade Delete is that it is self maintaining.

Imagine this scenario:

          TableB - - - - TableNew
         /
   TableA
         \
          TableC

When you add TableNew, with a cascade delete, you do not need to add any code to deal with deletions from either TableA or TableB.

With code managed deletes, you have at least two places to add new code to.

The main benefits I have found for managing deletes through code are:
1. It stops accidental deletes (Thank you foreign key violations!)
2. It can deal with the following scenario, where DDL often can't.

          TableB
         /      \ 
   TableA        TableNew
         \      /
          TableC

Answer 2

In my opinion the policy about deletes should be part of the model, for this reason is better if you define it using DDL during the creation/setup of your database.