Types of Keys in DBMS: Definition & Need

The keys in DBMS are instrumental in establishing relationships between different columns and tables in the database, enabling effective data retrieval and manipulation through queries and joins.

• In a relational database, a key plays a crucial role as it acts as a means to uniquely identify a row (or tuple) within a table (or relation). 
• Its primary function is to ensure that each entry in the table possesses a distinctive identifier, avoiding duplicate or ambiguous data. 
• Key values, the individual components of a key, hold the essential information that sets them apart from one another. 
• These values are carefully chosen to maintain data integrity and facilitate efficient data access
• Selecting appropriate keys is a fundamental aspect of database design, ensuring data accuracy, integrity, and the establishment of meaningful connections 

Key Terms: DBMS, PrimaryPrimary Key, Super Key, Candidate Key, Alternate Key, Foreign Key, Composite Key, Unique Key

---

Need of Keys in DBMS

[Click Here for Sample Questions]

In a database management system (DBMS), keys play a crucial role in ensuring data integrity and providing a unique identity to each record. Let's consider the example of a college student database to understand how keys work:

Attributes for Identifying Students Uniquely

Using student names as a key might not be a suitable choice because two or more students could have the same name, leading to ambiguity and potential data duplication.

• While using the department as a key might work for identifying students within a particular department
• It won't be unique across the entire college, as different departments might have students with the same names.
• Similarly, using sections as keys might be unique within a department but not across the entire college
• As different departments might have the same section names (e.g., Section A in both Computer Science and Mathematics departments).
• Using the year of study as a key can also lead to non-uniqueness, as multiple students may be studying in the same year.

University Roll Number as Primary Key

The most appropriate attribute for uniquely identifying each student in the college database is the university roll number.

• The university roll number is typically assigned to students when they enroll and remains unique throughout their academic journey in the college.
• By using the university roll number as the primary key in the database, each student record will have a distinct and unique identity.
• This primary key can be used to efficiently retrieve all other details related to a specific student, such as their name, department, section, and year.

Combination of Attributes as Composite Key

In some cases, a single attribute may not be sufficient to ensure uniqueness, especially in complex databases with multiple relations.

• In such situations, a combination of attributes can be used as a composite key to provide a unique identity to each record.
• For instance, if there is a table that stores student grades for different subjects, the combination of student roll number and subject code could form a composite key for identifying unique grade records.

Importance of Keys in Data Integrity

Keys are essential for maintaining data integrity within a database. They prevent duplicate or inconsistent records from being stored, ensuring accurate and reliable information.

• The primary key constraint enforces the uniqueness of key attributes, preventing the insertion of duplicate records into the table.
• Foreign keys establish relationships between tables, maintaining referential integrity and preventing invalid data entries that do not match related records in other tables.

Read More: Introduction To Array​

---

Different types of keys in DBMS

[Click Here for Sample Questions]

Keys are of seven broad types in DBMS:

• Candidate Key
• Primary Key
• Foreign Key
• Super Key
• Alternate Key
• Composite Key
• Unique Key

Primary Key

In a database, the primary key is a crucial concept for ensuring data integrity and uniquely identifying records within a table. Let's consider an example of a bookstore inventory database to understand the concept of a primary key.

Book Table - Attributes

ISBN (International Standard Book Number): A unique identifier assigned to each book.

• Title: The title of the book.
• Author: The author's name.
• Genre: The category or genre to which the book belongs.
• Publication Year: The year the book was published.
• Copies Available: The number of copies of the book available in the inventory.

Primary Key Selection

In the Book table, both ISBN and Title could be considered as candidate keys since they can uniquely identify each book.

• However, out of the candidate keys, only one can be chosen as the primary key.
• For this example, let's choose ISBN as the primary key for the Book table.

Characteristics of the Primary Key

The characteristics are mentioned below:

• Unique: The primary key contains unique values that uniquely identify each record (book) in the table. In this case, each book has a distinct ISBN.
• Individual Identification: The primary key can identify only one tuple (record) at a time. Each book can be uniquely identified by its ISBN.
• No Duplicate Values: Primary keys cannot have duplicate values. Each book in the inventory must have a unique ISBN.
• Non-NULL: The primary key cannot have NULL values. Each book in the inventory must have a valid ISBN assigned to it.
• Composite Primary Key: While a primary key is often a single attribute, in more complex scenarios, it can be a combination of multiple attributes. 
• For instance, if the bookstore is part of a larger library system, the primary key could be a combination of ISBN and Library Branch ID 

In the given example, the primary key "ISBN" serves as the unique identifier for each book in the Book table. This primary key ensures that each book's information is stored and accessed reliably and without duplication in the database.

Super Key

Consider a fictional online shopping database to understand the concept of a super key.

Customer Table - Attributes

CustomerID: A unique identifier assigned to each customer.

• Email: The email address of the customer used for login and communication.
• Phone: The phone number of the customer for contact purposes.
• Address: The customer's delivery address.
• Date of Birth: The birth date of the customer for age verification.

Super Key Selection

In the Customer table, several sets of attributes can serve as super keys, uniquely identifying each customer.

• CustomerID: The CustomerID alone can identify each customer uniquely.
• Email: The Email attribute can also uniquely identify each customer, as each email address should be unique in the system.
• CustomerID + Email: Combining the CustomerID and Email together will still provide a unique identity for each customer, making it a super key.

Characteristics of Super Keys

Characteristics of Super Keys are mentioned below:

• Unique Identification: Super keys can uniquely identify individual tuples (rows) in the table. 
• Each combination of attributes in the super key must lead to a distinct set of values.
• Combination of Attributes: A super key can consist of a single attribute or a combination of multiple attributes.
• Candidate Key: A candidate key is a special type of super key where no proper subset of the attributes can uniquely identify rows. 
• In our example, both CustomerID and Email are candidate keys as they meet this criterion.
• Support for NULL Values: Super keys can contain attributes that allow NULL values.
• For instance, in our example, the Date of Birth attribute may not be available for all customers, and thus, it may contain NULL values within the super key.

Example Super Key

In the Customer table, the combination of CustomerID + Email can be considered as a super key. This set of attributes guarantees a unique identity for each customer in the database. The table below illustrates a few sample records from the Customer table:

In this example, the super key (CustomerID + Email) ensures the uniqueness of each customer's information in the Customer table.

Candidate Key

Consider a fictional employee database for a company to illustrate the concept of a candidate key.

Employee Table - Attributes

EmployeeID: A unique identifier assigned to each employee.

• Social Security Number (SSN): The unique SSN assigned to each employee for tax and identification purposes.
• Email: The email address of the employee used for communication.
• DepartmentID: The identifier for the department where the employee works.

Candidate Key Selection

In the Employee table, several sets of attributes can serve as candidate keys, satisfying the condition of being minimal and uniquely identifying each employee.

• EmployeeID: The EmployeeID alone can uniquely identify each employee in the database, making it a candidate key.
• Social Security Number (SSN): The SSN is also a candidate key since it uniquely identifies each employee, and no other employee shares the same SSN.
• Email: The Email attribute, if set as a unique constraint in the database, can also be a candidate key, ensuring that each employee has a distinct email address.
• EmployeeID + Email: The combination of EmployeeID and Email together can form another candidate key, ensuring uniqueness.

Characteristics of Candidate Keys

The Characteristics are mentioned below:

• Unique Identification: Candidate keys contain attributes that can uniquely identify individual tuples (rows) in the table.
• Each candidate key guarantees a distinct set of values for each record.
• Minimal Set of Attributes: Candidate keys are minimal super keys, meaning that no proper subset of the attributes in a candidate key can uniquely identify rows.
• Each candidate key represents the smallest possible set of attributes required for uniqueness.
• Support for NULL Values: Candidate keys can contain attributes that allow NULL values. 
• For example, in our Employee table, the Email attribute may not be available for all employees, and thus, it can contain NULL values within a candidate key.

Example Candidate Keys

In the Employee table, three candidate keys can be identified:

• EmployeeID: The EmployeeID attribute is a simple candidate key that uniquely identifies each employee in the company.
• Social Security Number (SSN): The SSN attribute is another simple candidate key that guarantees uniqueness for each employee.
• EmployeeID + Email: The combination of EmployeeID and Email forms a composite candidate key, ensuring that each employee's identity is distinct.

Below is a sample record from the Employee table:

In this example, the candidate keys (EmployeeID, SSN, and EmployeeID + Email) ensure the uniqueness of each employee's information in the Employee table. From these candidate keys, one can be chosen as the primary key to uniquely identify each employee in the database.

Alternate Key

Consider a fictional car dealership database to illustrate the concept of an alternate key.

Car Table - Attributes

CarID: A unique identifier assigned to each car in the inventory.

• VIN (Vehicle Identification Number): A unique alphanumeric code assigned to each car for identification purposes.
• Licence Plate: The licence plate number of the car.
• Model: The model name of the car.
• Colour: The colour of the car.
• Price: The price of the car.

Candidate Keys and Alternate Key Selection

In the Car table, both CarID and VIN can serve as candidate keys as they uniquely identify each car.

• CarID: The CarID is a candidate key and is chosen as the primary key to uniquely identify each car in the database.
• VIN: The VIN is also a candidate key, but it is not chosen as the primary key.
• The candidate key that is not selected as the primary key is known as an alternate key.
• In this example, since the VIN is a candidate key but not the primary key, it becomes the alternate key.

Characteristics of an Alternate Key

The Characteristics are mentioned below:

• Uniqueness: Like candidate keys, alternate keys also contain attributes with unique values that can identify individual records (cars) in the table.
• Not Primary Key: An alternate key is a candidate key that is not chosen as the primary key.

Example Alternate Key

In the Car table, the VIN (Vehicle Identification Number) is an alternate key. Below is a sample record from the Car table:

In this example, the CarID serves as the primary key, and the VIN becomes the alternate key. Both keys ensure the uniqueness of each car's information in the Car table. While the primary key is chosen as the main identifier for the records, the alternate key (VIN) remains a candidate key with unique values for each car in the database.

Foreign Key

Consider a fictional university database to illustrate the concept of a foreign key.

Student Table - Attributes

The attributes are:

• StudentID: A unique identifier assigned to each student.
• Name: The name of the student.
• Department: The department in which the student is enrolled.
• Batch: The batch year of the student.

Course Table - Attributes

The attributes are:

• CourseID: A unique identifier assigned to each course.
• CourseName: The name of the course.
• Department: The department offering the course.
• Credits: The number of credits assigned to the course.

Enrollment Table - Attributes

The attributes are:

• EnrollmentID: A unique identifier assigned to each enrollment record.
• StudentID: A foreign key referencing the Student table, representing the enrolled student.
• CourseID: A foreign key referencing the Course table, representing the course in which the student is enrolled.
• Grade: The grade obtained by the student in the course.

Foreign Key Selection

In the Enrollment table, the attributes StudentID and CourseID serve as foreign keys. These attributes reference the primary keys in the Student and Course tables, respectively.

Characteristics of Foreign Keys

The characteristics are:

• Cross-Reference: Foreign keys act as a cross-reference between different tables. 
• They establish relationships between data in one table and the primary key data in another table.
• Secondary Key: In the referencing table (Enrollment table in this example), the foreign key acts as a secondary key, helping to identify and associate records from the referenced tables (Student and Course).
• Referencing and Referenced Relations: The table with the foreign key is called the referencing relation (Enrollment table in this example)
• While the table containing the primary key being referenced is called the referenced relation (Student and Course tables).
• Relationship Constraint: The value of the foreign key in the referencing table must match an existing value in the primary key of the referenced table, ensuring data consistency.

Example Foreign Keys

In the Enrollment table, the StudentID and CourseID serve as foreign keys, referencing the Student and Course tables, respectively. Below is a sample record from the Enrollment table:

In this example, the foreign keys (StudentID and CourseID) establish a relationship between the Enrollment table and the Student and Course tables. They allow us to link student enrollment records with specific student and course details from the respective referenced tables.

Composite Key

Consider a fictional e-commerce database to illustrate the concept of a composite key.

Order Table - Attributes

The attributes are:

• OrderID: A unique identifier assigned to each order.
• CustomerID: A unique identifier assigned to each customer.
• ProductID: A unique identifier assigned to each product.
• OrderDate: The date when the order was placed.
• Quantity: The quantity of the product ordered.

Composite Key Selection

In the Order table, a single attribute may not be sufficient to uniquely identify each order. Instead, a combination of attributes can be used to create a composite key that guarantees uniqueness.

• OrderID: The OrderID is a primary key and serves as the unique identifier for each order in the database.
• Composite Key: In scenarios where we want to quickly access details of an order based on the customer and the product, we can create a composite key using CustomerID and ProductID together.

Characteristics of a Composite Key

The characteristics are:

• Uniqueness: The combination of attributes in a composite key must lead to unique values for each record (order) in the table.
• Two or More Attributes: A composite key is made up of two or more attributes that are used together to identify rows.
• Secondary Key: In the absence of a primary key, a composite key can serve as the main unique identifier for each record in the table.

Example Composite Key

In the Order table, the combination of CustomerID and ProductID can be used as a composite key to access the details of an order. Below is a sample record from the Order table:

In this example, the OrderID serves as the primary key, uniquely identifying each order. Additionally, the combination of CustomerID and ProductID can be used as a composite key, allowing us to quickly access the details of an order placed by a specific customer for a particular product.

Unique Key

A unique key refers to a column or a set of columns that identify every record uniquely in a table. 

• All the values in this key must be unique. Unlike a primary key, a unique key is capable of having one NULL value.
•  In other words, a single row in the table can have a NULL value in the unique key column.

Also Read:

---

Things to Remember 

• Keys in a database management system (DBMS) are essential for ensuring data integrity and providing a unique identity to each record.
• Different types of keys include Primary Key, Super Key, Candidate Key, Alternate Key, Foreign Key, Composite Key, and Unique Key.
• The primary key is a special type of unique key that uniquely identifies each record in a table and cannot have NULL values.
• A super key is a set of attributes that can uniquely identify a tuple, and it may contain more attributes than necessary for uniqueness.
• Candidate keys are minimal super keys and are used to uniquely identify records.
• Each table must have at least one candidate key.
• A foreign key establishes a relationship between tables by referencing the primary key of another table. 
• It acts as a cross-reference between tables and maintains data integrity.

Read More: Tree Topology