Re: Separate foreign keys with shared ID space

"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.

Primary keys are an artifact of the physical model, where all bets are off because we now need to deal with implementation constraints such as lack of cascade update or performance implications. Due to the limited nature of SQL implementations there can be a substantial difference between a logical schema and its physical implementation. Received on Tue Aug 03 2004 - 01:44:10 CEST