Re: Cascading Deletes

From: Todd Taylor <nospam.taylor.todd_at_home.nospam.com>
Date: Tue, 13 Feb 2001 04:44:18 GMT
Message-ID: <CY2i6.273675$iy3.59232508_at_news1.rdc1.tn.home.com>


Peter / Kristian,

Let me re-phrase the example to clarify the point I was trying to make.

The system was orignally delivered with the ON DELETE CASCADE clause used with the foreign key declaration between order_header and order_detail. Only one DELETE statement is used by the system to delete an order. After a few months of use the customer changes their mind on some requirement in the system and, after some analysis, it is decided that the foreign key must be re-created _without_ the ON DELETE CASCADE clause. Had the application not been written to use the cascading delete, that portion of the code would not have to be re-coded, assuming someone remembered that it even needed changing.

Let's also say that, for whatever reason, it is valid that some order headers can exist without details. With that in mind, I'll ask again, if something happened to the system and you had to go behind the scenes to remove the bad headers, with cascading deletes on, are you sure you typed in the right surrogate key for the header table? Peter's query will not help because you now have to type in the IDs. Granted, nothing will protect you from deleting the valid "bereaved" headers inadvertantly, but not having the cascading deletes enabled protects the other orders.

Does this change anything for either of you? I think the specifics of the example were getting in the way before.

Thanks again.

TNT Peter Franklin wrote in message <3a87ac00.1523320_at_news.which.net>...
>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
>>
Received on Tue Feb 13 2001 - 05:44:18 CET

Original text of this message