Re: Cascading Deletes

Hi David

You have summed it up - no more to be said ! :-).

It is not a technical question, but depends on the real world requirements. Your two examples illustrate it perfectly.

Regards

Peter Franklin

On Sat, 10 Feb 2001 13:54:26 GMT, "David Cressey" <david_at_dcressey.com> wrote:

>I think a good case can be made either for or against cascading delete.
>Todd Taylor already made a good case against cascading deletes, so let me
>just raise the other side.
>
>There are times when orphaned rows in a child table cause a lot of trouble,
>and almost never offer any value.
>
>Let's take two tables, ORDER_HEADER, and ORDER_DETAIL. ORDER_HEADER
>provides all the context information for the entire order, like who the
>customer is, and the date the order was palced on, etc. ORDER_DETAIL
>provides data on each specific item purchased, like what product it was,
>the quantity, and the purchase price (unless the purchase price is solely
>a function of the product), etc.
>
>Now let's say an appropriate DELETE comes through for a given ORDER_HEADER.
>Are there any circumstances under which deleting the ORDER_DETAIL rows for
>that order would be a mistake? I think not. In this case, I think cascaded
>deletes are, in fact, the best way for the database to protect itself from
>program error or user error, and better than merely imposing a referential
>integrity constraint.
>
>But let me be careful about this. Let's say the two tables are DEPARTMENT
>and EMPLOYEE. And let's say that there's a foreign key reference to
>DEPARTMENT in the EMPLOYEE table, that says which departement a given
>employee is assigned to.
>
>Do I still have the same opinion? No, absolutely not. In this case, the
>two tables refer back to different entities, and I would rather use an
>integrity constraint to push an error back to the user, if the user
>attempted to DELETE a department that still had employees. The users would
>then decide whether to fire all the employees in the department or transfer
>them to other departments beofore deleting the department.
>
>So my opinion that cascaded deletes are useful depends on the relationship
>between the tables and the real world entities that the data describes.
>It's not a technical issue.
>
>
>--
>Regards,
> David Cressey
> www.dcressey.com
>"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 Sat Feb 10 2001 - 15:40:11 CET