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

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

On Jul 12, 2:15 pm, "David Cressey" <> wrote:
> "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.
In a relational perspective, the term *primary key* was first used by Codd to designate a specific unique identifier that allows to distinguish tuples.

> 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. But I can't see where
> the relational model necessitates it.
Who cares what SQL schools of *management* have to say about relational model? So far, these people have proven mostly a deep ignorance of what the relational model implies. I stopped paying attention to them and feel much better since. A primary key (unique tuple identifier) is mandatory to establish that a relation is a relation. A relation body can *not* be having dupplicate tuples. Else it is not a relation.

> On another subject, just what *is* the distinction between "logical" and
> "physical". Over the decades since James Martin wrote on the subject,
> there seems to have been considerable drift in what the terms actually mean.
> Perhaps we have too many Humpty Dumpties in the field!

In a relational perspective, one could view *logical* as dealing with defining the structure apects of relations, normalization, and what allows the system to make logical inferences to establish new defined facts.

*Physical* refers to *how* such logical system is actually physically implemented. A total separation of the two layers is imperative to allow for a system to be called relational (independence between the two layers).

To be coherent with such approach, my opinion is that primary key terminology should be avoided at all at physical level. Else one ends up making indexes and primary keys interchangeable. Direct image systems are the cause of such confusion (ORACLE, DB2, SQL Server)

Regards... Received on Thu Jul 12 2007 - 15:39:42 CEST

Original text of this message