Re: Does Codd's view of a relational database differ from that ofDate&Darwin?[M.Gittens]
From: Jan Hidders <jan.hidders_at_REMOVETHIS.pandora.be>
Date: Thu, 07 Jul 2005 21:17:18 GMT
Message-ID: <yZgze.139676$tY6.7414841_at_phobos.telenet-ops.be>
>> paul c wrote:
>>
>>> Jan Hidders wrote:
>>>
>>>>
>>>> Another small thing is updating primary keys. If a primary key has
>>>> accidentally been entered wrong and you want to fix that with an
>>>> update then it is usually not possible to simply update it, and the
>>>> problem gets even worse if it is also refered to by foreign keys. In
>>>> an ER model this is a non-problem.
>>>
>>> like a few other people (i suspect we are a minority), i think of
>>> 'update' as a sugaring or shortcut. ignoring transaction or
>>> concurrency issues, is there any logical difference between 'update'
>>> and the combination of 'delete-insert'?
>>
>> Splitting the update in a delete and an insert makes the situation
>> even more complex. Assume a relation S that has a foreign key to the
>> primary key of R. For an update to the PK of a tuple in R it is easy
>> to see what it means to cascade that update to S. If you split the
>> update in a delete and an insert this is not so simple because on the
>> delete you would have to either (1) delete the corresponding tuples in
>> S or (2) nullify their foreign keys, so if you then follow with only
>> the insert you will have lost information. So, yes, there is a logical
>> difference.
>
> if the PK in R was 'entered wrong', wouldn't the rows in S be wrong anyway?
Date: Thu, 07 Jul 2005 21:17:18 GMT
Message-ID: <yZgze.139676$tY6.7414841_at_phobos.telenet-ops.be>
paul c wrote:
> Jan Hidders wrote: >
>> paul c wrote:
>>
>>> Jan Hidders wrote:
>>>
>>>>
>>>> Another small thing is updating primary keys. If a primary key has
>>>> accidentally been entered wrong and you want to fix that with an
>>>> update then it is usually not possible to simply update it, and the
>>>> problem gets even worse if it is also refered to by foreign keys. In
>>>> an ER model this is a non-problem.
>>>
>>> like a few other people (i suspect we are a minority), i think of
>>> 'update' as a sugaring or shortcut. ignoring transaction or
>>> concurrency issues, is there any logical difference between 'update'
>>> and the combination of 'delete-insert'?
>>
>> Splitting the update in a delete and an insert makes the situation
>> even more complex. Assume a relation S that has a foreign key to the
>> primary key of R. For an update to the PK of a tuple in R it is easy
>> to see what it means to cascade that update to S. If you split the
>> update in a delete and an insert this is not so simple because on the
>> delete you would have to either (1) delete the corresponding tuples in
>> S or (2) nullify their foreign keys, so if you then follow with only
>> the insert you will have lost information. So, yes, there is a logical
>> difference.
>
> if the PK in R was 'entered wrong', wouldn't the rows in S be wrong anyway?
Yes, but if you tell the DBMS it is an update it might be able to infer how to fix them. If you only tell it about the insert + delete it cannot know that.
- Jan Hidders