Re: delete cascade
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 22 Apr 2007 17:33:00 GMT
Message-ID: <g%MWh.26985$PV3.280233_at_ursa-nb00s0.nbnet.nb.ca>
>
> (Haven't thought of a reply to the rest of the post yet but regarding
> the above, I must bring up a sidetrack even though I don't understand it
> - I believe 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.)
Date: Sun, 22 Apr 2007 17:33:00 GMT
Message-ID: <g%MWh.26985$PV3.280233_at_ursa-nb00s0.nbnet.nb.ca>
>> ... >> Consider base relations you would likely find familiar with an Invoice >> relation, an InvoiceItem relation, and a foreign key reference from >> InvoiceItem to Invoice. >> >> In the first case, consider application views that essentially mirror >> the base relations. If someone tries to delete an invoice while >> invoice items remain, the dbms will balk with an error due to the >> foreign key constraint. >> >> In a second case, consider an application view that groups the invoice >> items as an RVA of each invoice. If one deletes an invoice, one >> deletes the invoice items at the same time and the dbms will not balk. >> ...
>
> (Haven't thought of a reply to the rest of the post yet but regarding
> the above, I must bring up a sidetrack even though I don't understand it
> - I believe 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 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."
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 whatever additional information is necessary to disambiguate the situation. Received on Sun Apr 22 2007 - 19:33:00 CEST