Is there a real advantage in managing cascading deletion in code rather than in a database?

Question

I am going to ask this question with some preconditions that may make the question inappropriate, but here it goes.

Assuming my database has a way to make cascading deletes, and I am not trying to encode a possible database change, and my database model is such that I always want a particular deletion to be cascaded, is there any advantage in managing this cascading deletion in the application code, and not execute it through DDL?

It seems to me that the additional code, the ability to "skip it" and the probability (possibly zero) of losing the built-in optimization of your own database functions are more significant than any possible wins.

Did I miss something?

Answer 1

The biggest advantage that I have had in the past, in DDL Cascade Delete, is that it supports itself.

Imagine this scenario:

TableB - - - - TableNew / TableA \ TableC 

When you add TableNew , with cascading deletion, you do not need to add code to handle deletions from TableA or TableB.

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

The main advantages that I found for managing removal through code:
1. Stop accidental deletion (Thanks for breaking the foreign key!)
2. It can deal with the following scenario where DDL often cannot.

  TableB / \ TableA TableNew \ / TableC 

Answer 2

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