Re: Constraints and Functional Dependencies
Date: Mon, 26 Feb 2007 15:06:21 GMT
Message-ID: <NHCEh.8671$tA1.1134_at_trndny02>
"Marshall" <marshall.spight_at_gmail.com> wrote in message
news:1172382132.346588.167980_at_s48g2000cws.googlegroups.com...
> On Feb 24, 2:08 pm, "Walt" <wami..._at_verizon.net> wrote:
> > "Marshall" <marshall.spi..._at_gmail.com> wrote in message
> >
> > news:1172333601.148573.19370_at_v33g2000cwv.googlegroups.com...
> >
> >
> >
> > > On Feb 24, 6:41 am, paul c <toledobythe..._at_oohay.ac> wrote:
> > > > Marshall wrote:
> > > > > ...
> > > > > With such a system, a relation R with attribute a (which I will
> > > > > write as R(a)) having a as a foreign key into S(b) is expressed
> > > > > as follows:
> >
> > > > > forall R(a): exists S(b): a = b
> >
> > > > > So we can express foreign keys this way.
> > > > > ...
> >
> > > > I presume that if S had other attributes besides b, this definition
> > > > would mean that b doesn't need to be a so-called primary key? (That
> > > > would be okay with me.)
> >
> > This is a digression, rather than another nit pick. The concept behind
> > "primary key" adds nothing, mathematically, to the concept behind
"candidate
> > key". Any one of the candidate keys could be used as a foreign key,
with
> > equal effect.
>
> Agreed.
> >
> > In practice, it turns out to make data management easier if, wherever
there
> > are tables with multiple candidate keys, one of them is chosen as the so
> > called "primary key", and all foreign key references to that table are
made
> > by way of that key.
>
> Can you list a few ways primary keys make things easier?
Yes, but with the caveat that they probably don't have any theoretical significance.
The selection of primary keys makes index design a little easier. To some extent, successful index design depends on anticipating traffic patterns. It's easier to do this if primary keys are documented and used.
The selection of primary keys shortens the learning curve for a new database builders. When revising and extending a schema, a database designer will have to make use of candidate keys to reference existing tables. There is some learning required about the semantics of the key chosen. By selecting and documenting primary keys, existing database builders make it easier to learn the existing tables.
The next one only applies to people who don't eschew NULLs. I'm one such person. It can be useful, under certain circumstances to "demote" a candidate key to non-key status, by permitting NULLS in that column (those columns). However, it would can be harmful to do so, if the candidate keys has been used as the basis for foreign key reference. By choosing and documenting primary keys and excluding NULLS from them, one can avoid certain pitfalls without even understanding them.
Natural joins are easier to express, and easier to resolve. It depends on what metadata is used to set up the natural joins, I guess. But if you always use the primary key for reference, it strikes me that the resolution of natural joins has a smaller search space. Received on Mon Feb 26 2007 - 16:06:21 CET