Re: Cascading Deletes

Todd Taylor wrote:
>
> 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.

That various parts of the code is depended on each other is nothing new. Of course this requires you to analyse these dependencies when you make changes, which means hard work and risk of forgetting some things. In this specific case the dependency is between program code and DDL, but DDL is code too.

On the other hand, these dependencies cannot be totally avoided,and if you are trying to avoid it as much as you can, you will have to duplicate code in the system, making it harder to maintain in other aspects.

As I have said earlier I tend to favour the attitude that the DBMS should do as much work as possible. If then, at the initial design of the system, it makes sense that headers and headerdetails are seen as one entity, then I would use cascading deletes. When the customer arrives with changed specs 2 month later, I will make it clear to him, what the cost of changing ones mind is, then make the revision of the program if he's willing to pay. I will *not* take into account each and every possible change of rule when designing the system.

> 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.

Yes, the above example is so specific, that it does not make sense to discuss the general aspects of the problem on this basis. If you are absolutely sure, that there is only one place in the whole system, where deletion of headers take place, then the discussion is meaningless. Why? Because the problems involved in changing the system will be very minor, no matter which approach you used to start with. The trouble is: what will you do, when the system gets complex?

<snip>

Why, oh why can't you quote usenet-style? Either you are answering a point made in the post you are responding to, in which case it will clearer, if you put the response *below* the quote, or you are not, in which case you might as well delete the quote.

--
Kristian Damm Jensen              | Feed the hungry. Go to 
kristian-damm.jensen_at_capgemini.dk | http://www.thehungersite.com