SQL SERVER – Difference Between Candidate Keys and Primary Key

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)

18 thoughts on “SQL SERVER – Difference Between Candidate Keys and Primary Key

  1. 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.


  2. 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


  3. 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.


  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

    • A view(virtual table) is a temporary table that is formed out of one or more original tables.
      to create view
      create view

      to delete


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s