Keys in DBMS

In this course, we will study about keys, its types - primary, candidate, super, foreign, composite and compound keys.

What are Keys in DBMS

They are the attribute or set of attributes that are used to identify any record or row in the table.
Relationship between two tables can be known by the use of keys
Keys can be used individually or in a group to identify a record.

Why we need a key in DBMS

Identification of a record can be done easily. In real-world problems, a table might consist of thousands of rows and duplication of records can be there. Keys uniquely identifies the record irrespective of all the challenges.

Establishes the relationships and identifies the relationship between tables.
Identity and integrity can be enforced in the relationship.

What are the various keys in DBMS

There are various types of keys in DBMS enlisted below:

  1. Candidate key
  2. Primary key
  3. Super key
  4. Foreign key
  5. Composite key
  6. Compound key
Keys in DBMS

Student_id Student_name Phone_no Subject_id
1 Ash 2084748 1010
2 Aryan 3748592 2020
3 Aman 9384674 1010

This table represents the table <STUDENT> having attributes student_id, student_name, mobile_no and subject_id

Student_id Student_name Subject_instructor
1010 Maths Loren
2020 Physics Carel

This table represents the table <SUBJECT> having attributes subject_name, subject_instructor and subject_id.

Student_name Student_id
1 1010
2 2020
3 1010

This table represents the table <ENROLL> having attributes subject_id and student_name.

Candidate key

A candidate key is the minimal set of keys that can uniquely identify a record or data in the table. It is a super key consisting of no repeated attributes.
The primary key is selected from a set of candidate keys.
There should be at least one candidate key in the table.
In the above-given example in the table <STUDENT> we have student_name and phone_no as a candidate key.

Primary key

Super key

It is a set of one or more attributes that are used to identify a tuple uniquely.
It is a superset of the primary key.
In this example, employee_id and employee_number is the super key.

Emplyee_id Employee_name Employee_number
1 Park A909
2 Will B808
3 Sam C606

Foreign key

Teacher_id Teacher_name mobile_no
1 Hannah 9283938
2 Clay 8273993
3 Loren 9836538
Department_code Department_name
001 CSE
002 ECE
003 MSE

In this example, we have two tables, <STEACHER> and <SDEPARTMENT> in an institute. However, it is not possible to know which teacher is teaching in which department.

So in the <STEACHER> table, we will add the foreign key “Department_code”. Therefore we have created a relationship between the two tables now.

Teacher_id Teacher_name mobile_no Department_code
1 Hannah 9283938 001
2 Clay 8273993 002
3 Loren 9836538 001

Composite key

Combination of two or more attributes to form a key.
If a single attribute is not able to identify the row uniquely and form a key then composite keys are formed.
It can be or can not be part of the foreign key.

Compound key

It is also a combination of two or more attributes to form a key.
It can be a foreign key.