In the world of databases, DBMS keys play a crucial role in identifying and managing data efficiently. Keys are used to uniquely identify rows (records) in a table, establish relationships between tables, and enforce data integrity. Without keys, the database would not be able to differentiate between similar or duplicate records.
In this post, we will explain what keys are in DBMS, their types, and how they work with the help of examples.
What is a Key in DBMS?
A key in DBMS (Database Management System) is a set of one or more columns (fields) in a table that is used to identify rows uniquely. Keys help to prevent duplicate records, ensure data accuracy, and build relationships between different tables in a relational database.
Importance of Keys
- Ensure uniqueness of records
- Maintain data integrity
- Link tables with one another
- Speed up search and sorting operations
Types of Keys in DBMS
There are several types of keys used in DBMS. Each serves a specific purpose in database design and operations. Let’s look at the most common types:
1. Primary Key
- A primary key uniquely identifies each record in a table.
- It cannot be null and must be unique.
- A table can have only one primary key.
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
Here, StudentID is the primary key and will be unique for each student.
2. Candidate Key
- A candidate key is a column (or set of columns) that can qualify as a unique key in a table.
- A table can have multiple candidate keys, but only one is chosen as the primary key.
Example:
In a table of employees:
EmployeeIDEmail
Both can uniquely identify a person, so they are candidate keys.
3. Alternate Key
- An alternate key is a candidate key not chosen as the primary key.
- It is an alternate option for uniquely identifying records.
Example:
If EmployeeID is the primary key, then Email becomes an alternate key.
4. Foreign Key
- A foreign key is a field in one table that refers to the primary key in another table.
- It is used to link two tables together.
Example:
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);
Here, DeptID in Employees is a foreign key that refers to Departments.
5. Super Key
- A super key is a set of one or more columns that can uniquely identify a record.
- It may contain extra fields that are not necessary for uniqueness.
Example:
In a table:
StudentIDEmail
Both together can be a super key. Even StudentID alone can be a super key.
6. Composite Key
- A composite key is made up of two or more columns that together uniquely identify a row.
- Used when no single column is unique on its own.
Example:
CREATE TABLE CourseRegistrations (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID)
);
Here, the combination of StudentID and CourseID forms a composite key.
7. Unique Key
- A unique key ensures that all values in a column are different.
- Unlike a primary key, it can contain null values (but only one null per column).
Example:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
Here, Email is a unique key. Each email must be different.
Summary Table of DBMS Keys
| Key Type | Uniqueness | Null Allowed | Purpose |
|---|---|---|---|
| Primary Key | Yes | No | Uniquely identify records |
| Candidate Key | Yes | No | Potential primary key |
| Alternate Key | Yes | No | Unused candidate key |
| Foreign Key | No | Yes | Link between tables |
| Super Key | Yes | Varies | Any key that uniquely identifies |
| Composite Key | Yes | No | Multi-column unique identifier |
| Unique Key | Yes | Yes (once) | Prevent duplicate data |
Conclusion
Understanding DBMS keys is essential for designing reliable and efficient databases. They ensure data remains accurate, relationships are maintained, and queries perform well. Whether you’re a beginner in database management or a developer building applications, mastering keys will help you create better data structures.