7 Different Types of Database Keys Explained with Example

Shape Image One
7 Different Types of Database Keys Explained with Example

7 Different Types of Database Keys Explained with Example

Why there are so many different keys in a Database system?

This is the first question that struck me when I learned about Different Types of Database Keys.

Each key serves a different purpose in the database.

What is the role/use of a key in the database?

  • Keys play a very important role in DBMS. They are crucial for the arrangement of tables in the database.
  • Keys uniquely identify records or a combination of records from huge database tables.
  • Database keys are also useful in establishing a relationship between one table with other tables.

In this article, we will discuss various database keys and their application by considering instances of the database table.

Usually, many find it difficult to distinguish different keys in the database. By going through each one of the keys explained, you can easily find the difference between any keys.

7 Different Types of Database Keys

First, let’s have a look at different types of keys in the database:

  1. Primary Key
  2. Candidate Key
  3. Super Key
  4. Alternate key
  5. Composite or Compound Key
  6. Unique Key
  7. Foreign Key

Database Table Example:

It is easy to explain the database keys by taking table examples. In this article, we are going to consider Employee table with the following keys.

different types of database keys

Emaployee Table

These columns names are self-explanatory to understand anyone what kind of data stored in each column. So I so skip explaining it.

  1. Primary Key

The primary key is the most important key in the database. There can be only one primary key in a table.

It will not accept duplicate or null values. The primary key contains unique values.

For Example:

In the Employee table, Employee_Id is one such element that can never be the same for two employees. Thus, this can be the primary key for the Employee table.

So, the user can extract details of any employee from huge records of employees using the primary key.

If you use SQL select query command with employee_Id as where clause, it returns a single row.

Select * from Employee where employee_Id=203;

This select SQL query retains employee detail whose employee_Id is 203.

  1. Unique Key

The unique key is a set of one or more columns or fields of a table that can uniquely identify a record in the table. Other than the primary key there can also be other unique fields in a table.

The unique key cannot have duplicate values and can accept only one null value.

For Example:

Employee_PhoneNumber is another unique field and can be used to extract records. This Employee_PhoneNumber is a unique key.

  1. Super Key

A super key is a set of one or more keys that are used to identify data or records uniquely in a database table. It includes only those fields that have unique values.

For Example:

To form a super key, you can combine any table column with the primary key. In the above example, {Employee_Designation and Employee_Id} is a super key.

  1. Alternate key

The alternate key can be an alternative or a candidate for the primary key when needed but it is not the primary key. An alternate key is a function of all candidate keys except the primary key.

For Example:

In the Employee table, Employee_PhoneNumber will have unique values thus it can be used as an alternate key but it is not a primary key.

  1. Candidate Key

A candidate key is a set of one or multiple columns in a database table. It can identify a record uniquely just like a primary key. These are other unique columns that can become a primary key.

There can be any number of candidate keys that can be used in place of the primary key if required.

For Example:

In the Employee table, Employee_PhoneNumber and Employee_PanNumber are two unique fields that can be used as candidate keys.

  1. Composite Key

This is a combination of one or more columns that can uniquely identify the records in a table. The composite key can be a combination of primary and candidate keys.

For Example:

Consider the Employee table. We can use Employee_Id and Employee_PanNumber as composite keys to extracting data from the table.

  1. Foreign Key

A foreign key can be a common key in two database tables. Suppose a Company table where it has a column Employee_Id which is also present in the Employee table in which it is the primary key.

Using a foreign key we can identify records from multiple tables. It accepts duplicate values as well as null values.

Foreign key also helps you to reduce data redundancy.

For Example:

In the Employee database, we can use the primary key “Employee_Id” from the Employee table as a foreign key for the new Employee salary table.

If you are looking for writing assistance for your academic paper, you can take help from a service called write my essay with EssayHub. They are professional writers.

Final Thoughts on Database Keys:

Many of the programmers are still confused and difficult to understand the difference Between Database and DBMS.

We can save the data in the text file as well. One of the best advantages of DBMS over File System is a database key feature.

Finding the difference between database keys is one of the common tasks. I hope you will do better next time when you construct your own database for your project.

This is all about different types of database keys. If you have any query or if you find something not understandable, feel free to discuss in the comment.


  1. Thanks for this easy explanation. Till now I was thinking the primary key and candidate key are the same.

Leave a Reply

Your email address will not be published. Required fields are marked *