Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005

    Unanswered: When to use Alternate Key

    Hi Guys,

    I understand the function of primary and foreign keys,
    But what is alternate key, would appreciate if anyone can highlight
    to me a situation where we need to define an alternate key.

    I know the defination of alternate key, what im confused is when do we
    use it, the purpose of it, and how is it different from primary key

    thanks in advance

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    An alternate key (the correct term, though "alternative key" would be better English) is a column or set of columns other than the primary key that is also a unique key for the table. Some tables will have them, some will not. If you recognise that your table has alternate keys you should usually create UNIQUE constraints on them.

    An example (off the top of my head): a hotel room reservation table may have a primary key "reservation_number". However, since no room can be reserved twice on the same day there are also alternate keys of (room_number, from_date) and (room_number,end_date).

  3. #3
    Join Date
    Mar 2004
    OK I think Andrew's reply is complete (as always) but just my 2 cents to clarify a bit more

    There is a concept in RDM named by "Candidate Key" that by definition is a unique key. What does it mean?

    It means you can uniquely specify each tuple (a.k.a row, record...) by that but it is not just unique! It should be minimalist too: If you could specify each tuple using a 2 column key and also a 3 column key, the 3 column one is not a Candidate key (BTW,They called this super key: do not panic!).

    After you determine the candidate key collection (yes they may be more than one CK but all should have the same number of columns), you choose one of them as your Primary key and other remaining CKs would be called Alternative keys.

    There is no obvious usage for Alternate keys in my opinion because you have (and should have) a chosen PK so the remaining CKs are just good in designing phase of database. Note that RDM is silent about choosing PK but you should compare the potential CKs from some practical view of point such as performance.

    -Good luck

  4. #4
    Join Date
    Feb 2005
    Thanks for the explanation, now things seems a bit more clearer on my side..

Posting Permissions

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