.png)
First of all let us understand what is a Key? A Key is an attribute or a set of attributes which uniquely identifies every tuple or record(row) in a relation(table).
Depending on a relation the Keys can either be formed using one or two columns.
For example:
Employee
In the above Employee table the EMP_ID is the Primary Key which uniquely identifies every employee record.
Types of keys in Relational Model
1. CANDIDATE KEY : It is the set of minimum attributes which can easily identify a tuple in a table. Every table must have at least one candidate key. A table can have more than one candidate keys.
Properties of a Candidate key -
It must contain unique values.
It can be made up of multiple attributes, making it a composite candidate key.
No candidate key should have a NULL value.
Example: EMP_ID is the candidate key in the table given below.
2. PRIMARY KEY : It is a column or a group of columns which identifies every row in a table. An entity can contain multiple keys in a table. The key which is most suitable from the table become a primary key.
Properties of Primary Key -
It must be unique and not null.
Every row must have a primary key.
If any foreign key refers to a primary key, then its value cannot be changed or updated.
Example : EMP_ID and EMP_EMAIL together or separately can become the primary key of the table given below.
3. SUPER KEY : It is an attribute or a set of attributes which uniquely identifies a row in a table. A super key should not be confused with a candidate key as there is a basic difference between both of them, i.e., a super key is a super set of all the possible keys in a table whereas a candidate key is chosen from the given set of super keys; the chosen candidate key should not contain redundant attribute/es.
Example :
Following sets are the super keys of the above given table:
{EMP_ID}
{EMP_EMAIL}
{EMP_ID, EMP_NAME}
{EMP_ID, EMP_EMAIL}
{EMP_ID, EMP_NAME, EMP_EMAIL}
{EMP_EMAIL, EMP_NAME}
4. FOREIGN KEY : It is a column(attribute) that creates a relationship between two tables. It points to the primary key of another table. Its purpose is to maintain data integrity and allow a transit between two different instances of an entity.
Example:
From the above, it is clear that EMP_DEPT act as a foreign key in table 1 and a primary key in table 2.
Follow us on Instagram @programmersdoor
Join us on Telegram @programmersdoor
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed.
Follow Programmers Door for more.