Re: Cascading Deletes

Hi Todd

I was wrong - there is more to be said <g>

I think your objection to David's order example is not valid. As he pointed out, there is a real difference between order lines and employees. An order line is _part of_ an order and is meaningless without it.

In your example, if the client means _orders_, then the cascade delete is irrelevant, since the order header will not be deleted. If he means _order lines_ it is still irrelevant, since an order line may be deleted whether or not cascade deletes are in operation, and if it is to be kept, then the header must be kept as well.

And if I had to clear out bereaved (is that the opposite of orphan?) headers, I would use a query which checked for them, not do it by hand.

Having said this, I must admit that I have never _ever_ used cascading deletes, even in situations like the order example. They just give me a nasty feeling that something may go wrong. I don't use coding either, but make the user delete the details before the master, with a pop-up form to help them. It makes them think about what they are doing.

 So I think that although theoretically I agree with David, I am perhaps on your side after all <g>.

Regards

Peter Franklin

On Sun, 11 Feb 2001 21:39:51 GMT, "Todd Taylor" <nospam.taylor.todd_at_home.nospam.com> wrote:

>I have to disagree with you both. Real world requirements are guaranteed to
>change. (Please also refer to my reply to Goran in the other thread)
>
>Let's take the Order example given below. Let's say a few months after you
>have delivered the system, the client comes to you and says, "I don't want
>my people to be able to delete Orders that have already been filled." Your
>cascading delete is no longer valid. How big is the ripple effect in your
>delivered system? Would you not have been more protected from this if you
>had simply used standard RI between the tables?
>
>Regardless of the answers to the previous questions, how hard is it to write
>"DELETE from order_detail where order_id = x" before deleting from
>order_header?
>
>Let's say that something happened to the system and the database has a bunch
>of headers with no details and the client wants you to clean them out of the
>database. With cascading deletes on, are you sure you typed in the right
>surrogate key for the header table?
>
>Thanks for both replies. I will have to argue against these same points
>back at the office. I enjoy the discussion.
>
>TNT
>
>Peter Franklin wrote in message <3a854f0a.1717133_at_news.which.net>...
>>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 Mon Feb 12 2001 - 10:58:25 CET