Different Keys In DBMS

Do we need key?

DBMS (Database Management System) stores large amount of data in such a way so that it will be easy to read, write and secure in efficient manner. It uses table format to organize data and store data in column and row format. However, data is stored in unordered format and difficult to identify unique record from the table.

Let us understand this concept with two simple tables which represent data about the employees and their respective department.

Employee Table

Code	Name	Email Address	Dept. Code	Phone Number	Address
01	John Smith	j.smith@example.com	3	123-456-7890	123 Main St, City, Country
02	Jane Doe	j.doe@example.com	1	987-654-3210	456 Elm St, City, Country
03	David Johnson	d.johnson@example.com	2	555-123-4567	789 Oak St, City, Country

Department Table

Code	Name	Location
1	HR	New York
2	IT	Banglor
3	Marketing	Singapore

Column represents the different attribute about entity. Age, Nationality, Passport Number, Email address etc. information for a user entity will be represented as different columns. In the employee table Code, Name, email address, phone number and address are the columns to represent individual employee details in the organization. Similarly in the department table code, name and location represent information about various departments in the organization.

Row represents information about the entity attributes. Each row in the table represents information about a particular entity. In real world application each table stores thousands or millions of entity information. To get records from table, we need to apply filter criteria on the different columns, but in some scenario, we get duplicate records from the database which leads to update or delete anomaly.

To solve this issue, we need to define key column in the table so using which we can update, delete or fetch unique record for an entity.

Keys

Key means a column or set of columns which identify unique row from the table. It is helpful when we required to find or select a particular row from the table. There are different types of key available in the DBMS (Database Management System) to identify unique row from the database table.

In the employee table we are multiple columns which helps us to identify unique records of the employee i.e. code, email address and phone number. We can consider all of them as Key for the table.

Super Key

Super Key is defined as a set of attributes within a table that can uniquely identify each record within a table. Super Key is a superset of Candidate key.

In our employee table, super key can be phone number, email, code or combination of any columns which identify unique records within a table.

Candidate Key

Candidate key is a column or set of columns which can be used to identify unique record from the table. 

• There can be more than one combination of Candidate keys in the table. 
• Candidate key cannot have NULL value. 
• Primary key can be any one of the Candidate keys.

In our employee table, Code, email, phone number and email plus phone number, code plus name columns can be considered as Candidate key.

Primary Key

The table can have more than one keys which helps to identify any row uniquely. Primary key is first and most suitable column which can be used to identify unique row from the table.

It can be Auto Increment Number, UUID or Timestamp when record created etc. 

In our example, Code column in both the tables are primary key because it will be unique auto increment number assigned to employee and department and never has NULL value.

This key will be used as reference in another table to define relationship between them.

Composite Key

When a primary key consists of more than one attributes/columns it is called Composite key. It is also called Concatenated Key.

In our employee table if we are combining email address and phone number column as primary key instead of code column then it is called Composite key.

Artificial Key / Surrogate Key

A Surrogate key is an artificial key that can distinctly identify every row in the table. It is unique, updatable, and can’t be NULL. Surrogate Keys of DBMS are allowed in certain cases when:

• The primary key is too big
• Complicated Primary Key
• Absence of key

Foreign Key

A Primary key which is used as reference key in another tables to establish relationship between two tables then it is called Foreign Key.

In our example each employee belongs to a particular department. Employee table stores department code as the reference key to indicate relation between employee and department.  Department code in the Employee table is treated as foreign key.

Table can have multiple foreign as it can have relations with multiple entities.

Unique Key

Unique key is similar to primary key, but it allows the NULL value in the column. 

It ensures that column(s) has unique value in the table. 

Unlike primary key, table can have more than one Unique Key. 

It also does not become the identifier of the row in the table.

I hope you liked the blog and able to understand the concepts of keys in the DBMS.

Thank you. Appreciate your feedback and comments.