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 (https://blog.sqlauthority.com)
18 Comments. Leave new
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.
However a unique key allows one null value.
not only just one null vaalue , unique will allow any number of Nulls.
Unique Key in case of SQL Server allows 1 null value whereas in case of Oracle multiple null values are allowed.
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?
If more than one columns are uniqui, then one of them will be a primary key and rest are candidate key.
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
Hi can you please let me know process of Normalization with example……
what is meant by view ? how to create and delete view ?
A view(virtual table) is a temporary table that is formed out of one or more original tables.
to create view
create view
as
from
where
to delete
delete
superb explanation !
please tell me how to use s_parse method in sql?
Nice explanation for all answers. Plz tell me what is a super key with an example?