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 (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Function, SQL Scripts
Previous Post
SQL SERVER – Fix : Error : SQLDUMPER library failed initialization. Your installation is either corrupt or has been tampered with. Please uninstall then re-run setup to correct to correct this problem. in a modal dialog with the title SQL Writer
Next Post
SQL SERVER – Create Multiple Filegroup For Single Database

Related Posts

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

    Reply
    • @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.

      Reply
  • 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

    Reply
  • how to identify condidate key plz tell me?

    Reply
  • avdhesh kumar
    March 22, 2012 2:24 pm

    how to create candidate key in a table .please give the example of candidate key

    Reply
  • Chris Stevenson
    May 30, 2012 7:46 pm

    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.

    Reply
  • Hi can you please let me know process of Normalization with example……

    Reply
  • sateesh sasapu
    July 26, 2013 5:50 pm

    what is meant by view ? how to create and delete view ?

    Reply
    • 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

      Reply
  • superb explanation !

    Reply
  • please tell me how to use s_parse method in sql?

    Reply
  • Nice explanation for all answers. Plz tell me what is a super key with an example?

    Reply

Leave a Reply