DBMS Keys

DBMS Tutorial What is DBMS Types of DBMS Components of DBMS Architecture of DBMS Three Schema Architecture DBMS vs File System DBMS Languages DBMS ER Model Components of ER Diagram DBMS Generalization DBMS Specialization DBMS Aggregation Relational Model Codd's Rule in DBMS Relational Model in DBMS Integrity Constraints DBMS Keys DBMS Relational Algebra Convert ER Model into Tables DBMS Joins

Here, we will learn about the various types of Keys in the database management system. Keys play an important role in the relational database management system for maintaining the data.

In the relational database, the key is the attribute or set of attributes which uniquely identifies the record/row/tuple of a relation or table. 
It also allows users to establish a relationship between the two Relations(Tables). Using keys, users can easily fetch specific data from the tables. 

Following are the different types of keys in the database management system:

1. Primary Key
2. Candidate Key
3. Foreign Key
4. Super Key 
5. Alternate Key
6. Composite Key

Primary Key

Primary Key is an attribute or set of attributes which allow the relational database to uniquely identify each tuple/row of the relation. 

The primary key is a key selected from the candidate key. So, it is a subset of the candidate key.

Following are the rules of primary key which must be followed by each user:

• The primary key attribute must contain some value in each row. 
• All the values of the primary key attribute must be unique.  
• Any cell in the primary key field cannot be left as NULL.
• If any foreign key is related to the primary key, then the values of that primary key can never be changed. 

Example:

In the following Employee table, Employee_ID acts as a Primary Key. 

Because Employee_ID is different for each employee. And, each employee has a specific ID if he/she works in the company, so it cannot be NULL.

Candidate Key

Candidate Key is same as the primary key. But, the only difference is, candidate key can be more than one in a table while the primary key can be only one for that table. 

The Candidate key is a key which is selected from the super key. So, it is a subset of the super key. 

This key can be the combination of two or more columns/attributes of the same relation.  

The attribute or set of attributes defined as the candidate key must contain unique values.

Example:

In the following Employee relation, {Emp_ID, Emp_EmailID} is considered as the candidate key, which uniquely identifies each employee of the table. 

Foreign Key

Foreign Key is the attribute of one table which relates to the primary key of another table in the relational database. 

This key allows database users to create a relationship between the tables. The main goal of this key is to maintain the data consistency and integrity of the tables.

This key references the primary key of another table, so it acts as a cross-reference between the relations.  

Example:

In database College, we cannot store the details of the department in the Student table. That's why we link the Department and Student table through the foreign key.

Here, the Department table is the referenced table and the Student table is the referencing table. 

In the Student Table, Department_Id selected as the Foreign key. In the Department table, Department _ID selected as a Primary key. 

Super Key

Super Key is the set of one or more keys which allows the database users to uniquely identify each tuple in the table. 

All the candidate keys are selected from this key, so it is a superset of candidate keys. 

Example:

In the following table, {Emp_ID}, {Emp_ID, Emp_Name}, {Emp_ID, Emp_EmailID}, and {Emp_EmailID} are the attributes or set of attributes which are selected as the super key, as they all can uniquely identifies each employee details of the table.

Alternate Key

Alternate Keys are those keys which are not primary key from all the candidate keys. This type of key is also known as secondary keys.  

Example:

In the following table, Employee_EmailID is selected as the Alternate Key. Because it is not created as the primary key from the candidate keys.

Composite Key

Composite Key is that key which contains two or more attributes for identifying the tuples/rows uniquely in the table. 

In some cases, we have to use the group of the attribute for identifying the records, so the composite key is used. 

Example: 

In the following Employee table, {Emp_ID, Department_ID} is selected as the composite key for identifying the employee details uniquely.