Candidate Key in DBMS

Candidate key in DBMS

 

In this article, we will learn about Candidate Key in DBMS.

  • Candidate keys are selected from the set of super keys, the only thing that you should remember while selecting candidate keys is, it should not have any redundant attitude
  • Hence candidate key also called as minimal super key
  • Definition of candidate key: Super key with no redundant attributes known as candidate key i.e should not contain any column that contains duplicate data.
Candidate key in DBMS

A candidate satisfies the following properties

  • A candidate key column must be unique i.e all the columns that are involved in the candidate key also must be unique
  • A candidate key may have  more than one attribute
  • A candidate key column  will not contain any null value
  • Candidate key always selects a minimum column combination that helps to uniquely identify the record.

 

Example for Candidate Key

Consider the following table student 

IDNAMEPHONE
66Elena9789578575
67Haley8758273875
68Alex7847326558
69Elena7923857563

First, identify all the super keys present in the table, then eliminate the super keys that contain a column with duplicate data. Then the remaining superkeys  that are left or nothing but candidate keys

  1. {Id}: ID column will contain all unique values hence ID column is a candidate key
  2. {phone}: As no two students have the same phone number, it is not a redundant data column and  hence phone column is a candidate key
  3. {Id, phone}: As both ID and phone are unique for all students this combination is a valid candidate key
  4. {Id, Name}: This combination is not a candidate key because the name column may have duplicate values
  5. {Id, phone, Name}: This combination is not a candidate key because the name column may have duplicate values
  6. {Name, Phone}: This combination is not a candidate key because the name column may have duplicate values

Candidate keys available in the table  student

  • {Id}
  • {phone}
  • {Id, phone}

 

Candidate key versus super key 

  • First of all, you need to understand that all candidate keys are super keys this is because candidate keys are selected from super keys
  • Look for those keys from which we can remove any columns that contain duplicate data. In the above example, we have not chosen {Id, name} as candidate key because {Id} alone can identify a unique row in the table, and {Name} column is redundant.