Re: Cascading Deletes

I tend to side with you, Todd. If you look at it, CASCADE doesn't ensure RI any more or less than RESTRICT. The difference is what you want the DBMS to assume. With CASCADE you are having it assume that attempting to delete the parent IMPLIES the delete to all the children. With RESTRICT you are telling the DBMS to catch your application failing to do things in the right order.

It really comes down to philosophy, and there is no right or wrong answer (or rather, all the answers are both right and wrong <g>).

From where I sit, I'd look at the risks of each. Lets consider the unintended consequence - or rather consider what happens with each choice when it's "wrong". In the case of CASCADE, the data is gone and you have not only to fix the application, but you have to restore data. But that's not so simple as saying "restore from backup", because most likely the user will notice the data condition after other transactions have been put through. If you restore from backup, you lose all those later transactions with it. Messy. You can't "unring the bell".

Turn it around. Suppose you're using RESTRICT, and your application is intended to delete both the header and the line items. If you've got a bug in the app where it forgets to do the delete for the details first, all you'll get is an error message. The database has prevented the error, and recovering is a matter of correcting the application defect, but the database is intact (albeit in the worst case the business is "down" because a critical function doesn't work). I'd rather be down right away, and have to recover simply by fixing the app and leaving the database alone, than find out that the business has been running along in error without anybody knowing, and the cost of recovery is too high to make things right. You are also more likely to find such errors during testing and can thus fix them prior to release. Also, the feedback loop is much tighter in this error case. You make mistake, it goes "boom" right then and there. With CASCADE, you make mistake, and the "boom" happens in some report or graph that you don't look for until later. And then you have to play Sherlock Holmes to find out what could have been the cause.

That's why I prefer to use RESTRICT. Both RESTRICT and CASCADE will enforce the RI constraints. RESTRICT does so by catching your mistakes and letting you "do no harm". CASCADE, in my view, is a riskier thing because it will carry out your mistake rather than help you catch it.

I also am anti-trigger for anything but audit trail type stuff. In the first place, I worry about levels of nesting (and exhausting them), and in the second I worry about recursive firing. So my PREFERRENCE is to avoid CASCADE and to avoid triggers. That point of view is by no means universal. YMMV.

I do NOT buy the argument that says you do this so that other applications don't have to know as much about the data model and the RI rules. If they're writing to the database, the obligation is on them to understand all the issues. For that matter, I don't subscribe (anymore) to the practice of using a common database as an application-to-application interface. This is much better done by providing some sort of public interface via a library or component that they can call, or a published XML or other file specification, rather than by you sharing your data dictionary.

"Todd Taylor" <nospam.taylor.todd_at_home.nospam.com> wrote in message news:ZY_g6.255437$iy3.56695788_at_news1.rdc1.tn.home.com...
> My company is having an internal discussion about whether or not the
> practice of using cascading deletes in a database is good or bad, and I
> wanted to get some outside opinions on the subject.
>
> I am arguing that they should never be used.
>
> I prefer that the database protect me or anyone else from inadvertantly
> deleting data. It is not very hard to type in (or develop) extra DELETE
> statements for x number of child tables when attempting to delete a row in
 a
> parent table.
>
> Let's say that a bug has been found in an application ( never happens
> <grin>) and you have to go "behind the scenes" and remove some bad data.
 If
> you are not using cascading deletes, isn't it nice to know that you can't
> accidentally delete the good data? Yes it may be a little more cumbersome
> but the alternative could be asking the client if they have a good, recent
> backup.
>
> The issue of protability is another argument against using them. Some
> databases do not support such a feature (MSSql 7.0 for example). This
> argument is a little weak though since you could likely accomplish this
 with
> a trigger.
>
> The only arguments I have heard for using them is from developers who
 don't
> want to write the extra code. To me, that is a cop out for being lazy.
>
> Please respond with any comments and /or counter arguments you may have.
> There are always at least two sides to every story and I would like to
 hear
> from them all.
>
> Thank you,
>
> Todd Taylor
> ProActive Technology, LLC
>
>
>
>
>
Received on Tue Feb 13 2001 - 21:59:25 CET