Re: What are the design criteria for primary keys?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 03 Sep 2010 23:26:14 -0300
Message-ID: <4c81ae51$0$11823$9a566e8b_at_news.aliant.net>



Brian wrote:
> On Sep 3, 10:46 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> 

>>Brian wrote:
>>
>>>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.
>>
>>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.

Then, if at all possible, you would insist on having those, right? If at all possible, you want to have all 5 of the above.

What would happen if you used an irreducible key for a uniqueness constraint and used a reducible superkey for references to it? Received on Fri Sep 03 2010 - 21:26:14 CDT

Original text of this message