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

☆ It is an individual column or set of columns that can be used to uniquely identify each and every row in the table.
☆ The set of candidate keys is used to select the primary key.
☆ Only one primary key can be there in a table.
☆ No null value should be there in the primary key field.
☆ No modifications can be done in any value of the primary key field.
☆ In the above-given example in the table <SSTUDENT> we have student_id as a 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

☆ It is an attribute value that acts as a primary key in another table.
☆ Therefore it helps in linking two tables.
☆ Foreign key maintains the data integrity.
☆ It also allows navigation between different instances.
☆ It acts as a cross-reference.

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.