Re: Cascading Deletes

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

I don't see why not. All that has changed is that now you are not even allowed to delete ORDER_HEADER. But if you do it anyway (for example when cleaning out erroneosly created orders) you still want the cascading delete in order to maintain database integrity.

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

In my opinion this *is* a standard way of handling RI.

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

This is a question of how much you work you want to do yourself (each and every damn time and never forget it) and how much you leave to the DBMS. I tend to vote for the other approach: leave as much as reasonable to the DBMS, but be careful.

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

If not, I'm in DS whatever happens.

<snip>

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