Home > DB Concepts > DB Basics – What are Candidate, Primary, Composite & Super Keys and Difference between them?

DB Basics – What are Candidate, Primary, Composite & Super Keys and Difference between them?

September 15, 2014 Leave a comment Go to comments

Let’s take an example of an Employee table:

    DOB, -- Date of birth
    DOJ, -- Date of joining
    SSN, -- Social Security Number
    DeptID, -- Department ID
    MgrID -- Manager ID

1. Candidate Key: is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. A relation/table can have one or more than one Candidate Keys. A Candidate key is also known as a minimal Super key.

Here in Employee table columns EmployeeID & SSN individually can maintain uniqueness in a table, thus are eligible for Candidate keys. The columns EmployeeName + DOB combined can also make up a Candidate Key, but there is a narrow chance that 2 Employees with same name can be born in same day.

2. Primary Key: is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level. More about PK.

Here in Employee table you can choose either EmployeeID or SSN column for a PK, EmployeeID is preferable choice because SSN is a secure (PII) value.

3. Alternate Key: are the other Candidate key attribute/columns that you didn’t choose as Primary key column.

Like if you choose EmployeeID as a PK then SSN would be the Alternate key.

4. Super Key: is a superset of Candidate key. If you add any other attribute/column to a Candidate Key then it become a Super Key.

Like EmployeeID + EmployeeName is a Super Key.

5. Composite Key: If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique.

Like if there is no EmployeeID or SSN columns in Employee table, then you can make EmployeeName + DOB as a Composite Primary Key. But still there can be a narrow chance of duplicate rows.

–> Check the video:

DBMS Keys Types

  1. deepak
    September 6, 2016 at 9:36 am

    really very helping tute and really removes my doubts .. thanks manoj ji ………..

  2. sachin Khokhar
    September 28, 2018 at 1:49 pm

    There is some problem in composite key definition

  1. September 28, 2015 at 6:56 pm
  2. September 16, 2016 at 10:27 pm
  3. January 2, 2017 at 5:39 pm

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: