Introduction

In the vast realm of Database Management Systems (DBMS), keys play a pivotal role in maintaining
the integrity and efficiency of data. They serve as the fundamental building blocks that enable the
identification and organization of records within a database. A key is a characteristic or a group of
characteristics that uniquely identifies a row (or tuple) within a table (or relation). Keys are essential
for establishing relationships between various columns and tables in a relational database. The
specific values within a key are commonly denoted as key values. Understanding the various types of
keys is crucial for designing a well-structured and optimized database. In this blog, we’ll delve into
the key types in DBMS and their significance.

Why Are Keys Necessary In DBMS?

Keys play a crucial role in Database Management Systems (DBMS) by defining various types of
integrity constraints within a database. A database table represents a collection of records related to
various events, but there may be duplicates among these records. To uniquely and distinctly identify
each record without duplicates, keys are employed.

For instance, consider a database of college students. To uniquely identify each student, attributes
like names, departments, sections, years, or a university roll number can be used. Keys in DBMS can
be a single attribute or a combination of multiple attributes, serving the primary purpose of
providing each record with a unique identity.

Types Of Keys In DBMS

Let’s consider a hypothetical scenario where we have two tables: one for storing information about
students and another for storing information about the courses they are enrolled in.

CourseID | CourseName | Instructor | StudentID (Foreign Key) |

1. Primary Key

The primary key is a unique identifier for each record in a database table. It ensures that no two
records have the same value, and it cannot contain NULL values. The primary key is establishing
relationships between tables, facilitating data retrieval, and enforcing data integrity. Typically,
primary keys are implemented using single or multiple columns.
Example:

Primary Key (Students):

 Primary Key: StudentID

 Explanation: The StudentID uniquely identifies each student in the “Students” table.

Primary Key (Courses):

 Primary Key: CourseID

 Explanation: The CourseID uniquely identifies each course in the “Courses” table.

2. Unique Key

Similar to the primary key, a unique key ensures that each record has a distinct value. However,
unlike the primary key, a table can have multiple unique keys. These keys often enforce data integrity
on columns that need to be unique but are not intended for primary identification purposes.

Example:

Unique Key (Students):

 Unique Key: FirstName, LastName

 Explanation: This unique key ensures that there are no two students with the same
combination of first and last names.

Unique Key (Courses):

 Unique Key: CourseName

 Explanation: This unique key ensures that there are no two courses with the same name.

3. Foreign Key

A foreign key establishes a relationship between two tables by linking a column in one table to the
primary key in another. It ensures referential integrity, meaning that values in the foreign key column
must correspond to values in the primary key column of the referenced table. This relationship is
vital for maintaining consistency across the database and enables the creation of relational
databases.

Example:

 Foreign Key: StudentID

 Explanation: The StudentID in the “Courses” table is a foreign key that references the
primary key (StudentID) in the “Students” table, establishing a relationship between the two
tables.

4. Composite Key

A composite key is a key that consists of multiple columns to uniquely identify a record. While each
column may not be unique, the combination of columns ensures uniqueness. Composite keys are
particularly useful when a single column cannot uniquely identify records, and a combination of
columns is required.
Example: Composite Key (Students)

 Composite Key: FirstName, LastName

 Explanation: This composite key ensures uniqueness by considering the combination of both
first and last names.

5. Super Key

A super key is a set of one or more keys that can uniquely identify a record in a table. It is a broader
concept than a primary key and may include more columns than necessary for uniqueness. Super
keys help in understanding the possible combinations of columns that can uniquely identify records.

Example: Super Key (Students)

 Super Key: {StudentID, FirstName, LastName}

 Explanation: This super key includes all the columns in the “Students” table and can uniquely
identify each record.

6. Candidate Key

A candidate key is a minimal super key, meaning it is a set of columns that uniquely identifies each
record in a table, and removing any column from the key would result in a loss of uniqueness. The
candidate keys are potential choices for the primary key of a table.

Example: Candidate Key (Courses)

 Candidate Key: {CourseID, CourseName}

 Explanation: This set of columns is a candidate key because it uniquely identifies each
course, and removing any column would result in a loss of uniqueness.

7. Surrogate Key

A surrogate key is a synthetic key generated by the database system to act as a unique identifier for
records. It is often an artificially created numeric or alphanumeric value and is used when a natural
key is either not available or not suitable for use as the primary key. Surrogate keys simplify database
operations and enhance performance.

Example: Surrogate Key (Students)

 Surrogate Key: StudentID (auto-generated numeric value)

 Explanation: This is a surrogate key generated by the database system to act as a unique
identifier for each student, simplifying database operations.