Re: Cascading Deletes

BG wrote in message <3a89a14c_at_news1.ltinet.net>...
>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>).

Actually, I think your next three paragraphs pretty much make it clear that RESTRICT is always right and CASCADE is likely wrong.<g> I completely agree.

>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 couldn't have said it better myself. Why risk NOT catching mistake and allowing it to reak havok on the system?

>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 find it very interesting that you bring up triggers. The discussions about this at the office led to the question, "Are triggers just as dangerous as cascading deletes?" I tend to use triggers for things other than just audit trail stuff. Perhaps we should start another thread for this. I would be interested in hearing your thoughts / theories on where the line should be drawn on triggers.

>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.

Thank you very much for your reply. You put into words some arguments against CASCADEs that I had in my head but have been unable to articulate.

TNT
>"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 Wed Feb 14 2001 - 04:49:35 CET