Let us first try to grasp the definition of the two keys.
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.
I have illustrates the difference between a candidate key and a primary key in SQL Server.
1 Introduction
2 An Example to Understand Keys
2.1 Select a key that does not contain NULL
2.2 Select a key that is unique and does not repeat
2.3 Make sure that Primary Key does not keep changing
3 Selection of Primary Key
4 Quick Note on Other Kinds of Keys
5 Summary
Read Complete Article Here
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Can u plz explain what is difference between Composite Key and Candidate Key ?
Also what is importance of Unique Key in Candidate key
@Jasminder: A composite key is a primary key which has more than 1 column. For example consider the many to many relation between student and courses. Here we need another table , lets say studentcourses .
This studentcourses table will have student id and course id as its columns. This will form your composite key.
Candidate key is a set of columns which can uniquely identify the values in a table and can act as a unique key. One of these candidate keys will become the primary key and the rest will become alternate keys.
Unique key as the name suggests , is used to uniquely identify a value in a table. For example Emp_id etc. Every candidate key will act as a unique key. Unique key can never be a NULL value.
the main reason why it is called “composite key” is because it can only be made of two or more- but not one- attributes. a composite key is not just a primary key: it can be a candidate and superkey as well. but the original meaning lies in the fact that it is made up of more than one attribute
how to identify condidate key plz tell me?
how to create candidate key in a table .please give the example of candidate key
If you have a table for countries in the world, with an id number, the name of the country, a two letter abbreviation of the country, and the country code for calling, and of those values could be a candidate key (they are all expected to be different from each other) but the most likely primary key would be the id number of the row.
vary good example .
thanks sir