Re: delete cascade

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 18 Apr 2007 13:41:57 GMT
Message-ID: <FepVh.25684$PV3.261993@ursa-nb00s0.nbnet.nb.ca>

paul c wrote:

> David BL wrote:
> 

>> On Apr 18, 9:31 am, paul c <toledobythe..._at_oohay.ac> wrote:
>>
>>> Bob Badour wrote:
>>>
>>>> ...
>>>> I am still lost. When does it delete? When does it not delete?
>>>
>>>
>>> Oh, I was assuming a delete is not possible if it would cause the
>>> constraint to be violated. Just what would happen would depend on
>>> implementation, personally I'd prefer a result of "false" but I guess
>>> many people prefer exceptions, just as many prefer "delete cascade".
>>>
>>> p
>>
>> That's not consistent with your OP where you say
>>
>> ...Ie., why shouldn't delete always mean so-called "cascade"?
>>
>> It seems to me there is only one logical constraint
>>
>> item exists => inv exists
>> ...

> 
> As I saw it, Bob introduced another constraint, which I could call "NOT 
> CASCADES" and I tried to form it in an unconventional way with <AND> and 
> <OR> TTM equivalents, in other words in my view given Bob's constraint, 
> both relations have constraints.

Paul, I believe you are confusing two things. Whether the delete cascades has no bearing on the logic for expressing the condition. The <AND> and <OR> expression a la TTM would be the same in either case.

The normal effect of a constraint is simply to block any inconsistent update. You gave an english phrase suggesting all foreign keys should cascade deletes, and I gave a similar english phrase suggesting they should not. Both are useful.

One must keep in mind a foreign key declaration is a convenient shorthand. The plain foreign key declares a constraint and is all "what". The "on delete cascade" feature extends the constraint with a triggered procedure, which thus combines some "how" with the "what".

More and more, I am liking "on delete" or "on update" less and less. I suggest the appropriate place to handle the issue is in the view specification. If one wants "on delete cascase", one can present a view with the invoice items as an RVA.

>> This must be met on any snapshot of the DB.
>>
>> For this one logical constraint there are two possible "procedural"
>> constraints when deleting an invoice:
>>
>> delete cascade : delete invoice -> delete items
>>
>> no delete cascade : item exists -> don't delete invoice
>>
>> You can express the logical constraint in any way you like, but in the
>> end they are all equivalent. I don't see how it has any bearing on a
>> procedural constraint.

> 
> I don't know what the procedural constraint is.  I was trying to avoid 
> procedure.

If you want to avoid procedure, don't suggest making all foreign keys cascade deletes. Received on Wed Apr 18 2007 - 08:41:57 CDT