Results 1 to 5 of 5

Thread: Primary key

  1. #1
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266

    Unanswered: Primary key

    I have taken over the maintainance of a DB written by someone else. I have started to look at some of the table designs and come across a 'funny' The table has two primary keys, one set to duplicates OK. Correct me if I am wrong, but I thought a table could have only one primary key and that duplicates were a no no. Am I wrong here and if so, what would be the purpose of two PK's in the same table?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Your table apparently has a "multi-field" primary key. This is done when a single field cannot assure uniqueness, so two fields are assigned as Primary keys. Say you have a database that covers personnel of all Emergency Services, fire, rescue and police. Since so much is being made of Social Security Numbers beings exposed, this particular city has decided instead to identify their employees by badge numbers. The problem is that there is some overlap, i.e. some firemen and policemen have the same badge numbers, so this alone is not unique. To solve this, the Primary key is assigned to two fields; the employee's Service plus badge number. This is now a unique identifier.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A typo I suspect
    Quote Originally Posted by Missinglinq
    This is done when a single field cannot assure uniqueness, so two fields are assigned as Primary keys.
    This is done when a single field cannot assure uniqueness, so two fields are assigned as the Primary key.

    Just like Highlander - there can be only one
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Thanks a bunch folks. You learn something every day.

    David

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In case you are interested....
    When you design a table you consider all the combinations of fields that would uniquely describe your entity (each row). The only rule for this is that they should be the minimum number of fields that uniquely identify an entity. Usually you have only one of these combinations so your choice for primary key is simple.

    However sometimes there are several. In this case you now have several Candidate Keys. You would pick one of these to act as the Primary Key. Perhaps the data for one of these keys must be filled in so that would likely be a good one. The remaining keys are now known as Alternate Keys and would be added as unique constraints in your database. The time when you are most likely to see the alternate keys enforced are if you are using surrogate keys. Merely enforcing uniqueness to your autonumber and not to any alternate keys does not really enfore uniqueness at all.

    Coo - that was all on a whim. In case your appetite is whetted:
    http://r937.com/relational.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •