7 Different Types of Keys in DBMS Explained | Database Keys

Summary: You will learn all Types of Keys in DBMS in this article, seven different types of keys in DBMS such as Primary Key, Super Key, Candidate Key, Alternate Key, Foreign Key, Composite Key, Unique Key. All types of keys in DBMS play an important role, Let’s understand with an example in detail.

What are The Keys in DBMS?

A key in DBMS (DataBase Management System)is an attribute or a collection/set of attributes that facilitate uniquely identifying a tuple (or row) in a relation (or table). These keys are also used to establish relationships between the various tables and columns of a relational database. Individual values in a key are known as key values.

This article will cover everything you would like to understand regarding the keys in DBMS and attribute closure to find the Key of any relation (table).

Why are the Keys Required in DBMS?

A key is used in the definitions of several kinds of integrity constraints. Furthermore, a table in a database represents a collection of records or events for a specific relation. currently, there may be thousands and thousands of such records, some of which can be duplicated.
There should be the simplest way to identify every record separately and uniquely, which means that no duplicates. Keys permit us to be free from this hassle.
Let us take a real-life example of the database of every student studying in a medical college.

What attributes of the medical student do you think will uniquely identify/determine each of them? You could refer to a student by using their name, department name, admission year, and section. Or, you can mention only the university student roll number, and you can get all the other details from that.

A key could either be a combination of more than one attribute (i.e or columns) or simply one attribute. The main purpose of this is to give each record a unique identity.

Types of Keys in DBMS

There are mainly seven types of keys in DBMS such as:
1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Composite Key
6. Alternate Key
7. Unique Key
Let’s understand and look at each of them separately.

1). Primary Key

A primary key(PK) is a column of a table or a set of columns that helps to recognised/identify every record present in that table uniquely. Furthermore, there can be only 1(one) primary Key in a table. Also, the primary Key can’t have identical values repeating for any row. Each value of the primary key has to be different with no repetitions/duplicates.

The PRIMARY KEY (PK) constraint placed on a column or set of columns won’t permit them to have any null values or any duplicates. thus, one table can have only 1 primary key constraint. also, any value in the primary key can’t be changed by any foreign keys(FK) (as explained below) which refer to it.

Important Note:
The value of the primary key can’t be NULL.
The value of the primary key must always be unique.
The values of the primary key can’t be changed i.e. never updation is possible.
The value of the primary key must be assigned when inserting a record.
A relation is allowed to have only 1 primary key.

2). Candidate Key

Candidate keys in DBMS are those attributes that uniquely identify/recognize rows of a table. The Primary Key(PK) of a table is selected from one of the candidate keys. thus, candidate keys have identical properties to the primary keys explained above. as a result, there can be more than one candidate key in a table.

Important Note:
All the attributes in a candidate key are sufficient as well as required to identify each tuple(row) uniquely.
Removing any attribute from the candidate key fails in distinguishing or identifying each tuple uniquely.
The value of the candidate key must always be unique.
The value of the candidate key can’t be NULL.
It is possible always to have multiple candidate keys in relation.
Those attributes that seem/appear in some candidate keys are called prime attributes.

3). Super Key

Super Key is the set of all the keys that facilitate identifying rows in a table uniquely. this implies that all those columns of a table that are capable of identifying the other columns of that table uniquely will all be considered super keys.

Thus, a super Key is the superset of a candidate key (as explained above). The Primary Key(PK) of a table is picked from the super key set to be made the table’s identity attribute.

Important Note:
A super key is a set of attributes that can determine or identify each tuple uniquely in the given relation.
A super key isn’t restricted to having any particular number of attributes.
Therefore, a super key can consist of any number of attributes.

4). Foreign Key

Foreign Key(FK) is used to establish relationships between two tables. Furthermore, a foreign key will require each value in a column or set of columns to match the Primary Key(PK) of the referential table. Thus, foreign keys help to maintain data & referential integrity.

Important Note:
Foreign key references are the primary key of the table.
A foreign key can take only those values which are present in the primary key of the referenced relation.
The foreign key may have a name other than that of a primary key.
A foreign key can take the NULL value.
There is no restriction on a foreign key(FK) to be unique.
In fact, the foreign key is not unique most of the time.
Referenced relation may also be called the master table or primary table.
Referencing relation may also be called the foreign table.

5). Composite Key

Composite Key in DBMS is a set of two or more attributes that facilitate or help identify each tuple in a table uniquely. Furthermore, the attributes in the set may not be unique or distinctive when considered separately. However, when taken all together, they will ensure/confirm uniqueness.

6). Alternate Key

As explicit on top of, a table can have multiple choices for a primary key(PK). However, it can choose or select only one. So, all the keys that didn’t become the primary Key are referred to as alternate keys.

7). Unique Key

A unique key is a column or set of columns that uniquely identify or determine every record in a table. Therefore, all values will have to be unique in this Key. thus, a unique Key differs from a primary key(PK) because it can have only 1(one) null value, whereas a primary Key can’t have any null values.

Important Note:
It will be unique or distinctive for all the records of the table.
Once assigned, then its value can’t be changed means that it is non-updatable.
It may have a NULL value.

An example to Explain the Types of Keys in DBMS is:−

Table1: EMPLOYEES

Employee_ID	Employee_Name	Employee_Phone	Dept_ID
1	Anders	4523895612	101
2	Atticus	3256754155	102
3	Dechen	7800459613	101

Table2: DEPARTMENTS

Dept_ID	Dept_Name	Dept_Manager
101	Information Technology	Devid
102	Administration	John
103	HR	Shruti

Table3: ENROLL

Employee_ID	Dept_ID
1	101
2	102
3	101

The Super Keys in <EMPLOYEES> table are −

{Employee_ID}
{Employee_Phone}
{Employee_ID,Employee_Name}
{Employee_ID,Employee_Phone}
{Employee_ID,Dept_ID}
{Employee_Phone,Employee_Name}
{Employee_Phone,Dept_ID}
{Employee_ID,Employee_Name,Employee_Phone}
{Employee_ID,Employee_Phone,Dept_ID}
{Employee_ID,Employee_Name,Dept_ID}
{Employee_Phone,Employee_Name,Dept_ID}

{Employee_ID}

{Employee_Phone}

{Employee_ID,Employee_Name}

{Employee_ID,Employee_Phone}

{Employee_ID,Dept_ID}

{Employee_Phone,Employee_Name}

{Employee_Phone,Dept_ID}

{Employee_ID,Employee_Name,Employee_Phone}

{Employee_ID,Employee_Phone,Dept_ID}

{Employee_ID,Employee_Name,Dept_ID}

{Employee_Phone,Employee_Name,Dept_ID}

The Super Keys in <DEPARTMENTS> table are −

{Dept_ID}
{Dept_ID,Dept_Name}
{Dept_ID,Dept_Manager}
{Dept_ID,Dept_Name,Dept_Manager}
{Dept_Name,Dept_Manager}

{Dept_ID}

{Dept_ID,Dept_Name}

{Dept_ID,Dept_Manager}

{Dept_ID,Dept_Name,Dept_Manager}

{Dept_Name,Dept_Manager}

The Super Key in <ENROLL> table is −

{Employee_ID,Dept_ID}

1	{Employee_ID,Dept_ID}

We can see that the Candidate Key in table is {Employee_ID} or {Employee_Phone}
Also, the Candidate Key in table is {Dept_ID} or {Dept_Name,Dept_Manager}
The Candidate Key in table is {Employee_ID, Dept_ID}
The Primary Key in table is {Employee_ID}
As per data table, the Primary Key in table is {Dept_ID}
Similarly, the Primary Key in table is {Employee_ID, Dept_ID}
The Composite Key in table is {Employee_ID, Dept_ID}
The Secondary Key in table is {Employee_Phone}
Similarly, the Secondary Key in table is {Dept_Name,Dept_Manager}
{Dept_ID} is the Foreign Key of table and Primary key of table.

FAQs Related Types of keys in DBMS

1). What are keys in DBMS?

A key in DBMS (DataBase Management System) is an attribute or a set of attributes that facilitate or help to uniquely identify/determine a tuple (or row) in a relation (or table). Furthermore, the keys are also used to establish relationships between the different/various tables and columns of a relational database. Thus, individual values in a key are known key values.

2). What is Keys and its types in DBMS?

Keys in RDBMS permit you to establish a relationship between and identify or determine the relationship between tables. Therefore, Eight types of keys in DBMS are Super, Primary, Foreign, Candidate, Composite, Alternate, Compound, and Surrogate Key. Thus, a super key is a group of single or multiple keys that identifies or recognized rows in a table.