Re: What are the design criteria for primary keys?

On Sep 3, 12:20 am, Bob Badour <bbad..._at_pei.sympatico.ca> 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 StackOverflow.com 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.
>
> http://tinyurl.com/ignorancetothefifth
>
> 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.

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. Received on Fri Sep 03 2010 - 15:52:10 CEST