Re: Cascading Deletes

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

>

>

>

>

>