.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.
Great stuff keet it up
Amazing