Understanding Keys in Relational Database
Keys are an integral element of Relational database model. These are used in identifying and establishing relationships between tables. Moreover, keys are necessary in recognizing any row of data or record in a table. These can either be a group of aspects or a single aspect wherein the combination of the same would act as a key.
In order to understand Keys in a better way, we should consider the following two tables-
(table 1)
| Student |
| Name |
| Student_ID |
| Course |
(table 2)
| Personal Information |
| Name |
| Address |
| Social_Security_Number |
| License_Number |
| Passport_Number |
We can see in the table no. 1 that the Student_ID of each student would be unique, which makes it the key for this relational database, wherein the table no. 2 contains Social_Security_Number of each person, which acts as the key in this table.
Different types of Keys
There are four various types of keys – Primary Key, Candidate Key, Foreign Key, and Super Key. To understand these in detail, read further.
Primary Key
This is the first key that helps in identifying a single instance of an entity, in a distinct manner. There can be numerous keys present in a table, which can be seen in the “Personal Information” table. That one key that is most suitable among other key attributes in a table becomes the Primary Key.
For instance, if we take the Personal Information table, we can see that along with the Social_Security_Number, the License_Number too can become the Primary Key as both these attributes are unique to respective individuals. The selection of any primary key depends on the developer and the specific needs.
Candidate Key
This is a set of columns or a single column which helps in uniquely recognizing any database record without referring to any other information. Any attribute apart from the Primary Key is known as the Candidate Key. It is as strong as the Primary Key. For instance, in the Personal Information table, the Social_Security_Number is the Primary Key whereas the License Number or the Passport Number are Candidate Keys.
Foreign Key
This is that attribute of a table that is used in identifying a Primary Key of another table. To understand this type of Key in depth, we will consider the following tables-
| Employee |
| Name |
| Employee_ID |
| Social_Security_Number |
| License_Number |
| Department_ID |
| Department |
| Department_ID |
| Department_Head |
In these two tables, we see that every employee is from a separate department, which is again an individual entity and therefore cannot be added to the same table of “Employee”. Therefore, it has been added separately to “Department” table. So, to link both these tables, the Primary Key of one table is added to another. So, in the Department table, Department_ID is the Primary Key, which has been added as a Foreign to the Employee table that helps in relating these two tables.
Super Key
A combination of columns that helps in uniquely recognizing any row in a table. Candidate Key is related to Super Key wherein the latter is a superset of the candidate key.