Re: Separate foreign keys with shared ID space

"David Best" <davebest_at_usa_dot_net> wrote in message news:huidnfwA_d9XTJPcRVn-sA_at_speakeasy.net...
> "mAsterdam" <mAsterdam_at_vrijdag.org> wrote in message
> news:410e1cc0$0$14941$e4fe514c_at_news.xs4all.nl...
> > Marshall Spight wrote:
> > > Hans Forbrich wrote:
> > >>Marshall Spight wrote:
> > >>>Christian Antognini wrote:
> > >>>>A PK should have no business meaning.
> > >>>
> > >>>Says who? Can you justify this statement?
> > >>
> > >>A PK should be selected to uniquely identify an entity. Ideally,
> > >>and by formal definition, the PK is invariant.
> > >
> > > Whose formal definition? Relations are sets; the only formal
definitions
> > > I'm aware of are those of set theory. Set theory does not have
"primary
> keys"
> > > in it; only candidate keys. Set theory doesn't say anything about sets
> > > being invariant. And keys aren't "selected" to be unique; they are
> unique
> > > or they aren't keys. Every set must have at least one key.
> >
> > At some point of time in the _table_ lifecycle
> > a primary key is decided on. Not because of
> > set theory, as you pointed out; there is no such
> > thing as a primary key, set-theory-wise.
> > Yet it is a high-impact decision - a change of
> > primary key later on can be a costly operation.
> >
> > I wonder ... is this where set theory leaves us out
> > in the cold? No use for set theory after this decision?
> > Which theories *are* available? Or do we leave the realm of theory
> > immediately when we move from relations to tables?
> >
> > Invariancy is a desirable property for primary keys,
> > no doubt. But - theoretically - why?
> >
> > Sorry for being vague - Invariancy already had a
> > crucial role in Ollongren's (and Dijkstra) treatment
> > of structuring information (around/before 1974), but
> > I could only come up with pragmatic reasons. So I guess I missed
> > something - maybe somebody can help me by pointing out what.
>
> The logical model has candidate keys, for which invariancy does not apply
as
> a defining characteristic. Any invariancy requirement would be imposed by
> an external predicate, and should be modeled using a constraint. Possibly
> there is an auditing requirement which necessitates tracking any changes
to
> the candidate key values; again something which can be modeled at the
> logical level.

>

I was in total agreement until the preceding statement. Codd didn't introduce primary keys as a physical construct, so I don't really buy the argument that they are entirely physical, especially for the reasons given. I don't understand what performance implications there would be that would favor a primary key over any other indexed key. However, I can see where a primary key might be useful in giving an RDBMS a deterministic hint as to what key to favor when multiple choices of keys might be possible, particularly with semantic optimization.

I regard the primary key a limitation of logic that is not necessary. Unless someone has a good argument against just using candidate keys and some other more sound rationale for justifying the promotion of a primary over other keys.

Due to the limited nature of
> SQL implementations there can be a substantial difference between a
logical
> schema and its physical implementation.
>
Yes, but keys don't fall into this category. Keys are logical constructs and behave in terms of expected logical behavior. How they are implemented is of little consequence as long as they behave as we expect.

>

• Dan