What are Keys and why it is needed?
Keys in the relational model of the database are the set of attributes that help in uniquely identifying a row or tuple in a table. Keys help in identifying a particular data in a table by uniquely identifying a tuple. They also help in identifying the relationship between different tables.
Types of Keys
There are various types of keys in the relational model of the database.
| student | |||||
|---|---|---|---|---|---|
| roll_no | name | stream | age | aadhar_no | mob_no |
| 1 | Ashish | CSE | 22 | 23747xxxxxxx | 8789086342 |
| 2 | Ankit | IT | 21 | 43747xxxxxxx | 8969505123 |
| 3 | Prashant | CSE | 21 | 75373xxxxxxx | 6299912752 |
| 4 | Ujjwal | CSE | 21 | 32793xxxxxxx | 7808516143 |
| 5 | Prashant | IT | 20 | 64535xxxxxxx |
| enrollment | ||
|---|---|---|
| roll_no | name | course |
| 1 | Ashish | DBMS |
| 2 | Ankit | OS |
| 1 | Ashish | CN |
Candidate Key
It is the minimal set of attributes that can identify a tuple uniquely. A candidate key must have a unique value in each row. A candidate key can't have a NULL value in any row.
eg. In the above table ‘student’, roll_no is a candidate key as it has distinct values in each row and it is not null in any row. Other candidate keys in this table are aadhar_no, (name, stream).
(name, stream) can be called a composite candidate key as they both together can uniquely identify a tuple.
Super Key
It is the set of attributes that can uniquely identify a tuple. The only difference between Candidate Key and Super Key is Candidate Key is the minimal set of attributes that can identify a tuple. Every candidate key is a super key but every super key is not a candidate key.
Any number of attributes added to a candidate key will be a super key.
eg. In the above-given table ‘student’, roll_no, aadhar_no is a candidate key, and (roll_no, Aadhar Number is a super key.
Primary Key
A Primary Key is a set of attributes that can uniquely identify the tuples. It is one of the candidate keys which is most suitable to identify a tuple. The value of the primary key in any row can't be null. There can be only one primary key in a table.
eg.
In the above-given table ‘student’, out of all candidate keys (roll_no, aadhar_no, etc), roll_no is the most suitable attribute for the primary key as it can never be the same for two students and it can't be null. So, it can be chosen as the primary key of the table.
Alternate Key
All other candidate keys except the primary key are called Alternate Key.
eg. In the above-given table ‘student’, all candidate keys except roll_no which is a primary key is alternate key. aadhar_no is a candidate key but not a primary key so it is an alternate key.
Foreign Key
It is the set of attributes that establishes a relationship between two tables. It refers to a column of a table that is the primary key of that table. The foreign key may have duplicate values and can also be null. The attribute which refers to the primary key of another table is called the foreign key.
eg. In the above-given table ‘enrollment’, roll_no is a foreign key to the roll_no in the ‘student’ table. It refers to the roll_no attribute of the student table which is the primary key in that table.
Unique Key
It also identifies a tuple of a relation uniquely. A table can have more than one unique key. There can be one null in a unique key column. All values in this column must be unique.
eg. aadhar_no can be a unique key in the above table as it is unique for all the citizens of the country. Unique key can also accept one null value like here if a person doesn't have an Aadhar card.
