Various Types of Keys in DBMS

The different types of keys in DBMS are −

• Candidate Key - The candidate keys in a table are defined as the set of keys that is minimal and can uniquely identify any data row in the table.
• Primary Key - The primary key is selected from one of the candidate keys and becomes the identifying key of a table. It can uniquely identify any data row of the table.
• Super Key - Super Key is the superset of primary key. The super key contains a set of attributes, including the primary key, which can uniquely identify any data row in the table.
• Composite Key -  If any single attribute of a table is not capable of being the key i.e it cannot identify a row uniquely, then we combine two or more attributes to form a key. This is known as a composite key.
• Secondary Key - Only one of the candidate keys is selected as the primary key. The rest of them are known as secondary keys.
• Foreign Key - A foreign key is an attribute value in a table that acts as the primary key in another table. Hence, the foreign key is useful in linking together two tables. Data should be entered in the foreign key column with great care, as wrongly entered data can invalidate the relationship between the two tables.

An example to explain the different keys is −

<STUDENT>

<SUBJECT>

<ENROLL>

The Super Keys in <Student> table are −

{Student_Number}
{Student_Phone}
{Student_Number,Student_Name}
{Student_Number,Student_Phone}
{Student_Number,Subject_Number}
{Student_Phone,Student_Name}
{Student_Phone,Subject_Number}
{Student_Number,Student_Name,Student_Phone}
{Student_Number,Student_Phone,Subject_Number}
{Student_Number,Student_Name,Subject_Number}
{Student_Phone,Student_Name,Subject_Number}

The Super Keys in <Subject> table are −

{Subject_Number}
{Subject_Number,Subject_Name}
{Subject_Number,Subject_Instructor}
{Subject_Number,Subject_Name,Subject_Instructor}
{Subject_Name,Subject_Instructor}

The Super Key in <Enroll> table is −

{Student_Number,Subject_Number}

The Candidate Key in <Student> table is {Student_Number} or {Student_Phone}

The Candidate Key in <Subject> table is {Subject_Number} or {Subject_Name,Subject_Instructor}

The Candidate Key in <Student> table is {Student_Number, Subject_Number}

The Primary Key in <Student> table is {Student_Number}

The Primary Key in <Subject> table is {Subject_Number}

The Primary Key in <Enroll> table is {Student_Number, Subject_Number}

The Composite Key in <Enroll> table is {Student_Number, Subject_Number}

The Secondary Key in <Student> table is {Student_Phone}

The Secondary Key in <Subject> table is {Subject_Name,Subject_Instructor}

{Subject_Number} is the Foreign Key of <Student> table and Primary key of <Subject> table.

David Meador

Updated on: 30-Dec-2020

21K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started