Re: delete cascade

From: paul c <toledobythesea_at_oohay.ac>
Date: Mon, 23 Apr 2007 00:52:09 GMT
Message-ID: <ZqTWh.115944$6m4.50822@pd7urf1no>

mAsterdam wrote:
> Bob Badour wrote:
>

>> paul c wrote:
>>
>>>
>>> ... some people say that you shouldn't delete from such a view, 
>>> assuming it is defined by using join.  I gather their reason is that 
>>> there isn't just one set of changes to the base tables that will 
>>> falsify the tuples in the view, eg., it would be enough to delete 
>>> just the base invoice items and not the base invoices to make the 
>>> view empty and they would like a logical reason for that decision 
>>> compared to deleting from both base tables or vice-versa.

>
>
> I think they are mixing (DBMS) capabilities and language issues.
> ...

I have been in a few debates about this, not entirely sure that I was right that joins and unions can always be deleted from and inserted to (assuming no related constraints), and read some comments by David McGoveran on the dbdebunk site that also suggested they can be. Part of my uncertainty comes from not being confident that I understood the opposing arguments.

I gather TTM 3rd edition (well, the book has been re-titled but I'm sure many here know which book I'm talking about) has an appendix giving some of the arguments pro and con. I don't have the book and I'm wondering if anybody here who does would care to characterize the arguments in the appendix?

(Otherwise, I suppose I'll have to break down and buy it, not that I should resent buying a book by authors who I greatly admire, it's just that after nearly ten years, I still haven't finished the 2nd edition!)

p

>> I look at that and ask: Is the join theoretically updatable? 

>
>
> Which is an uninteresting question, because both paulc's interpretation
> and Bob's, this one:
>

>> I look at that and ask: Is the join theoretically updatable? And I 
>> answer with: "Yes, not only is it updatable but we have our choice of 
>> update strategies."

>
>
> seem quite acceptable.
>

>> Granted, absent any other information, one has no theoretical reason 
>> to favor one update strategy over another, which is why a dbms must 
>> provide some means to express 

>
>
> s/express/accept/
>

>> whatever additional information is necessary to disambiguate the 
>> situation.

>
>
> Most DBMS already have this (accept) capability. The language the user
> or the program uses to interact with the DBMS to access their data
> should provide the freedom to express the usage of these capabilities.
>
> This may seem like a nitpick. To me the issues are clearly distinct, and
> but I often encounter this (what I consider a) blur.
Received on Sun Apr 22 2007 - 19:52:09 CDT