In this course, we will study about keys, its types - primary, candidate, super, foreign, composite and compound keys.
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.
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.
There are various types of keys in DBMS enlisted below:
This table represents the table <STUDENT> having attributes student_id, student_name, mobile_no and subject_id
This table represents the table <SUBJECT> having attributes subject_name, subject_instructor and subject_id.
This table represents the table <ENROLL> having attributes subject_id and student_name.
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.
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.
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.
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.
It is also a combination of two or more attributes to form a key.
It can be a foreign key.