Re: more on delete from join

"Mr. Scott" <do_not_reply_at_noone.com> wrote in message news:VqydnQmqmaKYNQ7XnZ2dnUVZ_uudnZ2d_at_giganews.com...

> Assuming that your interpretation is the right one, trying to delete from
> a join view would be like trying to delete from a denormalized table. One
> of the reasons to normalize is to eliminate so-called update anomalies.
> For example, a table that contains both customer information and order
> information exhibits a delete anomaly. When the last order for a customer
> is deleted, that customer's information is deleted. Decomposing the table
> into a customers table and an orders table with a foreign key from orders
> to customers eliminates that anomaly. If we accept your interpretation,
> then trying to delete from a view that joins orders and customers
> reintroduces the anomaly.

The delete anomaly for unnormalized tables arises from the fact that delete operates on entire rows. If you could delete only part of a row, without deleteing
the other part, it woild be possible to avoid the particular delete anomaly you mentioned.

When the last order pertaining to a customer is deleted, you only delete the data pertaining to the order, but you keep the row in the table with the customer data intact.

So how the heck to do you delete part of a row without deleting the entire row? Easy. You set the data you are removing to NULL.

I'm not seriously suggesting this as a practice. I'm just showing how the updateable view question, the NULL question, and normalization are interconnected. Received on Tue Aug 25 2009 - 15:02:40 CEST