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:
1. Candidate key
2. Primary key
3. Super key
4. Foreign key
5. Composite key
6. Compound key
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 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.
☆ 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.
☆ 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.
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.