Re: What are the design criteria for primary keys?

On Sep 3, 10:26 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> 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?- Hide quoted text -
>
> - Show quoted text -

What would happen? The functional dependency from the irreducible key to the reducible superkey becomes part of the referencing relation schemata. Received on Sat Sep 04 2010 - 08:44:16 CEST