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

From: Cimode <>
Date: Thu, 12 Jul 2007 06:54:49 -0700
Message-ID: <>

On Jul 12, 2:30 pm, "Roy Hann" <specia..._at_processed.almost.meat> wrote:
> "David Cressey" <> wrote in message
> news:tPpli.9982$qu4.3078_at_trndny06...
> > "Jan Hidders" <> wrote in message
> >
> >> On 11 jul, 22:25, Cimode <> 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.
I have been asking myself whether Codd really rejected it or not (that's unclear). To my knowledge he used the term until 1985. Why would somebody as he would use a term he would finally reject (I am afraid we'll never know)

However, I may understand why he would have rejected it: since the term was misused by direct image proponents, he may have realized that it was a dangerous bag term. Nevertheless, the term is not to blame but rather what people made of it.

> > 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.
The underlying issue I am trying to point out is really the independence between the logical and physical layer. But thank you for your comment.

> > 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
Precisely. Even a physical table based (not index based) unique constraint on column subset is sufficient tool to implement a primary key (or unique identifier). That does not make that primary key a physical concept.

One can easily a system where a primary key is physically represented so differently from how one could think logically when he structures a relation, that it would be unwise to call it a primary key. I prefer the term *primary key implementation scheme*. To be quite frank, I wish there would be another term to allow them to be differentiated. Received on Thu Jul 12 2007 - 15:54:49 CEST

Original text of this message