BeginnersBook

Home / DBMS / Super key in DBMS

Super key in DBMS

By Chaitanya Singh

Definition of Super Key in DBMS: A super key is a set of one or more attributes (columns), which can uniquely identify a row in a table. Often DBMS beginners get confused between super key and candidate key, so we will also discuss candidate key and its relation with super key in this article.

How candidate key is different from super key?

Answer is simple – Candidate keys are selected from the set of super keys, the only thing we take care while selecting candidate key is: It should not have any redundant attribute. That’s the reason they are also termed as minimal super key.

Let’s take an example to understand this:
Table: Employee

Emp_SSN	    Emp_Number	 Emp_Name
---------   ----------   -------- 
123456789	226	 Steve
999999321	227	 Ajeet
888997212	228	 Chaitanya
777778888	229	 Robert

Super keys: The above table has following super keys. All of the following sets of super key are able to uniquely identify a row of the employee table.

  • {Emp_SSN}
  • {Emp_Number}
  • {Emp_SSN, Emp_Number}
  • {Emp_SSN, Emp_Name}
  • {Emp_SSN, Emp_Number, Emp_Name}
  • {Emp_Number, Emp_Name}

Candidate Keys: As I mentioned in the beginning, a candidate key is a minimal super key with no redundant attributes. The following two set of super keys are chosen from the above sets as there are no redundant attributes in these sets.

  • {Emp_SSN}
  • {Emp_Number}

Only these two sets are candidate keys as all other sets are having redundant attributes that are not necessary for unique identification.

Super key vs Candidate Key

I have been getting lot of comments regarding the confusion between super key and candidate key. Let me give you a clear explanation.
1. First you have to understand that all the candidate keys are super keys. This is because the candidate keys are chosen out of the super keys.
2. How we choose candidate keys from the set of super keys? We look for those keys from which we cannot remove any fields. In the above example, we have not chosen {Emp_SSN, Emp_Name} as candidate key because {Emp_SSN} alone can identify a unique row in the table and Emp_Name is redundant.

Primary key:
A Primary key is selected from a set of candidate keys. This is done by database admin or database designer. We can say that either {Emp_SSN} or {Emp_Number} can be chosen as a primary key for the table Employee.

❮ PreviousNext ❯

Comments

    • Lee says

      Emp_Name cannot be both because names are not unique e.g. there could be hundreds of Jack inside the database.

    • Gabriele says

      Emp_Number is a super key, in fact with just Emp_Number we can lead up to Emp_Name or Emp_SSN;

      Emp_Name is NOT a super key because we can have 2 “Steve” or 3 or 4 in that table.. Name are not unique, we cannot say the same for SSN codes or Emp_Number. Bye
      Gabriele

    • Chaitanya Singh says

      I have added more details on this in the guide, Please refer the added section above.

  4. lavanya says

    I have doubt that
    {Emp_SSN, Emp_Number} pair also Candidate Keys?.because both are not a redundant attributes

    • Chaitanya Singh says

      No {Emp_SSN, Emp_Number} pair is not a candidate key because {Emp_SSN} alone is sufficient to identify a unique row in table. The same applies for {Emp_Number}. So based on this we can say that {Emp_SSN} and {Emp_Number} both are candidate keys but {Emp_SSN, Emp_Number} is not a candidate key.

    • Chaitanya Singh says

      There can be number of candidate keys present in a table, however there is only one primary key. A primary key is always chosen from a set of candidate keys. The decision of choosing primary key from a set of candidate keys is made by database admin.

  6. Sai kumar says

    why not the emp_name is the part of candidate key .though it is also having unique set of values???

  7. keerthi says

    candidate keys are always come with pair so, why{Emp_SSN, Emp_Number} is not a candidate key