Re: A pk is *both* a physical and a logical object.

"David Cressey" <cressey73_at_verizon.net> wrote in message news:tPpli.9982$qu4.3078_at_trndny06...
>
> "Jan Hidders" <hidders_at_gmail.com> wrote in message
> news:1184241371.515071.251680_at_k79g2000hse.googlegroups.com...
>> On 11 jul, 22:25, Cimode <cim..._at_hotmail.com> wrote:
>> > Furthermore...
>> > <<Technically a PK is *only* a physical implementation device, not a
>> > logical concept at all.>>
>>
>> `When I use a word,' Humpty Dumpty said, in rather a scornful tone,
>> `it means just what I choose it to mean -- neither more nor less.'
>>
>> `The question is,' said Alice, `whether you can make words mean so
>> many different things.'
>>
>> `The question is,' said Humpty Dumpty, `which is to be master --
>> that's all.'
>>
>> ;-)
>>
>> To answer the question, I think that is quite simple. As defined in
>> the relational model it is a logical concept. As far as I know the SQL
>> standard does not state that a PK implies an index (but I could be
>> wrong) and then it is also there a logical concept. If it does imply
>> an index then it is mixed concept because it has both logical and
>> physical consequences.
>>
>> -- Jan hidders
>>
>
> It was my understanding that the relational model defines keys, but not
> primary keys. That is, any candidate key is as much of a key as any
> other.

I can't lay hands on the reference but I believe Codd did flirt briefly with the concept of a primary key, but he later rejected it. He would have been right to do so.

> It was my understanding that certain schools of data management,
> including
> the SQL school, adopted the convention of naming one candidate key as
> primary key, and of making all FK references refer to that key, where
> possible. I can see, and use, that practice myself.

I have considerable trouble with PKs even in practice. Noticing that databases have a lifetime comparable to the lifetime of the enterprise, and at least an order of magnitude longer than any application, it troubles me when I am asked to nominate one candidate key to be preferred over all others for the lifetime of the enterprise. That is potentially a very, very long time. Who can see that far in the future? Apart from some (frankly worthless) SQL shorthands, PKs contribute nothing valuable and very possibly conceal or mislead in the long run.

> But I can't see where
> the relational model necessitates it.

On related point, some products that silently introduce an index to support either a primary key or unique constraint also allow you to specify explicitly that no index should be created (as for instance when there is already a suitable index).

Roy Received on Thu Jul 12 2007 - 15:30:41 CEST