Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Cascading Deletes

Re: Cascading Deletes

From: Todd Taylor <>
Date: Sun, 11 Feb 2001 21:39:51 GMT
Message-ID: <HEDh6.266140$>

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 <>...
>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.
>Peter Franklin
>On Sat, 10 Feb 2001 13:54:26 GMT, "David Cressey" <>
>>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
>>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
>>a function of the product), etc.
>>Now let's say an appropriate DELETE comes through for a given
>>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
>>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
>>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
>>then decide whether to fire all the employees in the department or
>>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.
>> David Cressey
>>"Todd Taylor" <> wrote in message
>>> 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
>>> 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.
>>> you are not using cascading deletes, isn't it nice to know that you
>>> accidentally delete the good data? Yes it may be a little more
>>> but the alternative could be asking the client if they have a good,
>>> 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
>>> a trigger.
>>> The only arguments I have heard for using them is from developers who
>>> 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
>>> from them all.
>>> Thank you,
>>> Todd Taylor
>>> ProActive Technology, LLC
Received on Sun Feb 11 2001 - 15:39:51 CST

Original text of this message