Re: What are the design criteria for primary keys?

From: Brian <>
Date: Fri, 3 Sep 2010 18:39:05 -0700 (PDT)
Message-ID: <>

On Sep 3, 10:46 am, Bob Badour <> wrote:
> Brian wrote:
> > On Sep 3, 12:20 am, Bob Badour <> wrote:
> >>Choosing good *primary keys* and candidate keys is a vitally important
> >>*database design* task--as much art as science. The design task has very
> >>specific design criteria.
> >>*What are the criteria?*
> >>As an experiment, I asked the above question on having
> >>first verified it wasn't already answered on the site. It is a very
> >>important question that has a very simple and clear answer. I had
> >>planned to offer a sizable bounty if nobody gave the correct answer
> >>after the 1st day and answer it myself if nobody claimed the bounty.
> >>
> >>Five people identified as John Saunders, David Stratton, Claudio Redi,
> >>wallyk, and rockinthesixstring voted to close it with nothing
> >>approaching a correct answer supposedly because "It's difficult to tell
> >>what is being asked here. This question is ambiguous, vague, incomplete,
> >>or rhetorical and cannot be reasonably answered in its current form."
> >>I thought the question was clear enough. The answer, of course, is:
> >>uniqueness, irreducibility, simplicity, stability and familiarity.
> >>To anyone who uses that site as a resource, all I can say is: Caveat lector!
> > The answer, of course, is:
> > uniqueness, irreducibility, familiarity, and if at all possible,
> > simplicity and stability.
> The criteria are what they are. One could just as easily rewrite them as
> "simplicity, simplicy, familiarity, and if at all possible, uniqueness
> and irreducibility" without really changing the meaning of anything.

Now you're just being ridiculous! Uniqueness and irreducibility are necessary: a candidate key is not a candidate key unless it has the uniqueness and irreducibility properties.

> They are a list of criteria that sometimes conflict requiring one to
> make design tradeoffs.
> > There should be no reason to use surrogates, so I agree on
> > familiarity, but not every candidate key is simple, and while
> > stability is a worthwhile goal, it is not always achievable.
> I try not to should on anyone or anything; although, I did make a recent
> exception.
> All keys are surrogates. A natural key is merely a familiar surrogate.
> While not every candidate key is simple, during database design, one
> still chooses whether to express references with a compound candidate or
> a simpler alternative.- Hide quoted text -
> - Show quoted text -
Received on Sat Sep 04 2010 - 03:39:05 CEST

Original text of this message