Different between Pimary key and Alternative Key

Posted on 2011-10-11
Last Modified: 2012-05-12
What's the difference between Pimary key and altnerative key ? Under what situtation should I need to define an alternative key ?

Question by:AXISHK
    LVL 2

    Accepted Solution


    A candidate key is one that can identify each row of a table uniquely.
    Generally a candidate key becomes the primary key of the table.

    If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

    A key formed by combining at least two or more columns is called composite key.

    Hope this sheds some light on the subject. If so, please mark as answer.
    LVL 9

    Assisted Solution

    To put it simply take an example, look at the Customer table below:

    create table Customer ( ;
    CustKey      I primary key, ;
    CustID      c(8) unique, ;
    CustName c(30), ;

    CustKey must be unique because it is the PK for the table. However, the CustID must also be unique because it is used by the users to locate a specific customer record. This causes the CustID field to be an AlternateKey.

    Another example for the need of alternate key: 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).

    Hope this helps!

    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how the fundamental information of how to create a table.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now