Candidate Key & Primary Key - Database

#Candidate Key

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

#Primary Key

A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

...

One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.

There are few points to consider while turning any Candidate Key into a Primary Key.

• Select a key that does not contain NULL
• Select a key that is unique and does not repeat
• Make sure that Primary Key does not keep changing

#Summary
A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules - 1) Not Null, 2) Unique Value in Table and 3) Static - are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key, the decision should be made by experienced DBAs who should keep performance in mind.

Source : Pinal Dave - Via SQL Authority Blog