Candidate key and primary key

Hi,

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 Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

for more info, please refer

http://blog.sqlauthority.com/2009/05/30/sql-server-difference-between-candidate-keys-and-primary-key/

hope this help

Hi Akshay,

 

A table may have more than one combination of set of columns that could uniquely identify the rows in a table. Each combination is a Candidate key. 

We pick one of the Candidate keys to be the Primary key.

Primary Key dont have NULL values, where as Candidate key column may contain one NULL value record.

and By default Primary key is Clustered index.

Hi Akshay,

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.

Refer link

http://blog.sqlauthority.com/2009/10/22/sql-server-difference-between-candidate-keys-and-primary-key-2/

Thanks