Types of Keys in DBMS: Everything You Need to Know

Think about a global e-commerce platform with millions of customers, orders, and products. One wrong click can vanish a customer’s order. Here comes the DBMS to organise data for businesses.

Every DBMS has keys that work behind the scenes to ensure the data is accurate, accessible, and secure. In his book Database Design and Relational Theory, C.J Date discusses using keys to safeguard data integrity and shape data.

DBMS is like a vast library; the keys are the catalogue system to help you find the right book. Understanding of the types of DBMS keys is required to manage your data in an orderly fashion.

Let's use some examples to help us understand the various types of keys in DBMS and their roles. We'll encounter a range of keys, from primary keys to foreign keys. Let's get started!

Keys in a Database Management System (DBMS) are attributes, or a set of attributes, to uniquely identify the records in a table and the relationships between tables. Keys are necessary for data integrity, as they prevent duplication and assure accurate and rapid retrieval of data, as well as maintaining the relationship through entity and referential integrity.

For instance, a Customer ID, which serves as the primary key in a customer database, distinguishes individuals who may have the same name. The primary key ensures that each record is uniquely identifiable. Different forms of keys act as safeguards to maintain the uniqueness of records. To create, read, update, or delete (CRUD) a record, it must be uniquely identified so the system knows exactly which one to work with. Uniqueness helps ensure that changes are applied to the correct record. Overall, keys in a DBMS preserve order and reliability, enabling organisations to trust their data and make better decisions, leading to improved operational efficiency.

1. Candidate Key

A candidate key is a minimal group of attributes that uniquely identifies each row in the table, and there can be many candidate keys. A candidate key has to be unique and non-null; no two rows can have the same value. In a university database, a StudentID and Email can be candidate keys for students, as both can uniquely identify students in the university system.

SQL Query

StudentID can be a candidate key
Email can also be a candidate key (Unique constraint)
PRIMARY KEY: StudentID is the primary key
UNIQUE: Email is a candidate key (but not selected as Primary Key)

2. Primary Key

A primary key is a unique identifier that identifies each record in a table. Primary keys are critical to data integrity since they cannot have null values and contain unique values to show that their records can be distinct. For example, a resident management system might have UserID as a primary key, where UserID will uniquely identify a user and their credentials and activity without duplicating records or confusion.

SQL Query

UserID column, which is the Primary Key
PRIMARY KEY: UserID uniquely identifies each user

3. Foreign Key

A foreign key is defined as a set of attributes in one table that refers to the primary key and unique key of another table, creating a relationship and ensuring referential integrity. This ensures consistent data relationships between tables. For example, refer to the following tables. The Purchases table uses ClientID as a foreign key to link to the Clients table to ensure that orders are being made by valid customers.

Clients:

Purchases:

SQL Query

Primary Key: ClientID (uniquely identifies each customer)

ClientID column (Foreign Key references the Clients table)
Primary Key: PurchaseID (uniquely identifies each purchase)
Foreign Key: ClientID (references ClientID in the Clients table)

4. Super Key

A super key is a set of attributes that can uniquely identify a record in a table, including both minimal and non-minimal combinations of attributes. Every candidate key is a super key, but not all super keys are candidate keys, as they may include extra attributes.

For example, in a library database, BookID, Title, and Author together can have the possibility to be a super key, but BookID alone would be a candidate key.

SQL Query

BookID, Title, Author: Super key (can uniquely identify records, but not minimal)

5. Alternate Key

An alternate key is a candidate key that is not selected for the primary key but can still be used to identify records uniquely. Once a primary key is determined, the rest of the candidate keys are called alternate keys and can be used as alternate unique identifiers.

For example, in an employee database, if EmployeeID is the primary key, then Email is an alternate key (must not be null) and can be used for lookups because it can be used to identify employees uniquely.

SQL Query

EmployeeID as the Primary Key. Email as an Alternate Key

6. Composite Key

A composite key consists of two or more attributes that together uniquely identify a record in a table. It is required when a single attribute cannot ensure uniqueness, often in many-to-many relationships.
E.g., if we had a course enrollment system, we could have a composite key that consists of both StudentID and CourseID. Now we can uniquely track each student's enrollment in each specific course, including duplication of Student and Course values across the table.

SQL Query

Composite Key consisting of StudentID and CourseID

7. Unique Key

A unique key has a single but slightly different function from a primary key. A unique key guarantees that all values in a particular column are unique from one another and may accept null values depending on the DBMS (a primary key does not). Unique keys are commonly used to implement uniqueness for non-primary attributes, like email addresses or phone numbers, where one null entry may be permissible.

SQL Query

CustomerID as a unique identifier and the Primary Key
Email is a Unique Key

8. Secondary Key

A secondary key is an attribute for searching or indexing, but it does not uniquely identify the records. The purpose of a secondary key is for improving query performance, not for maintaining uniqueness. A possible real-world scenario is a ProductName in a product inventory system. The ProductName is an indexed Secondary key to speed up product searches, for improved usability in an app for retail.

SQL Query

ProductID as a unique identifier and the Primary Key
ProductName is indexed, acting as a Secondary Key

1. Uniqueness and Identification
The primary and unique keys in a DBMS ensure uniqueness since they prevent duplication and confusion of data. For example, the ProductID that identifies products in a retail database.

2. Relationships Between Tables
In a relational database, foreign keys link tables by connecting a column in one table (like CustomerID in Orders) to a primary key in another (Customer). This keeps data accurate and consistent. In e-commerce, it helps prevent errors, speeds up queries, and supports systems like CRM, supply chain, and finance.

3. Data Integrity
Data integrity in a DBMS is maintained using keys like primary, foreign, and unique keys. Primary keys ensure each record is unique, while foreign keys prevent invalid references. For example, in healthcare, a PatientID uniquely identifies patients, and foreign keys ensure only registered doctors are linked to them. This is crucial for compliance with laws like GDPR and HIPAA. Good key management helps prevent costly mistakes and protects a business's reputation.

4. Efficient Data Retrieval
Keys in a DBMS help quickly find records by indexing primary and secondary keys, which boosts speed and accuracy. For example, indexing DeliveryDate in logistics lets users quickly track shipments. In real-time systems like online banking or e-commerce, this can improve data retrieval by enhancing user experience and operational efficiency.

5. Optimised Query Execution
Keys in a DBMS are essential for fast query execution and efficient database performance. Indexing primary, unique, or secondary keys speeds up complex queries and reduces computing load, saving time and money. For instance, a composite key like AccountID and TransactionDate can help financial services filter transactions quickly with minimal processing impact. This leads to lower infrastructure costs, better performance, and scalable growth without losing speed or reliability.

1. Candidate Key vs. Super Key: They Are Not the Same

Misconception: Candidate keys are often confused with super keys as interchangeable terms.

Clarification: A super key may possess redundant attributes, whereas a candidate key is a ‘minimal super key’, meaning that its attributes will never have a subset that can uniquely identify records.

Example: In the context of a library database, {BookID, Title, Author} is a super key, however, BookID alone is a candidate key as it gives the ability to uniquely identify the book independently of the other fields, provided it exists.

Importance: Recognising this distinction is beneficial in accurately modelling a database schema with properties that add to efficiency, and minimise storage expense as well as query performance penalties.

2. Foreign Key Doesn’t Have to Be Unique

Misconception: A foreign key has to be unique in its table.

Clarification: A foreign key references a primary key or unique key from another table and does not need to be unique. It may (and often will) have duplicate values.

Importance of Not Having to Be Unique: This feature allows for the possibility of one-to-many or many-to-many relationships in the database.

Example From Life: In an e-commerce database, the Orders table will have a CustomerID foreign key. The Orders table will allow multiple orders to share the same foreign key as a way to indicate that a customer can place multiple orders.

Customers:

Orders:

Here, CustomerID in the Orders table is a foreign key and is not unique, as C001 appears multiple times.

3. Primary Key Can Be Composite

Misconception: A primary key must be a single attribute. 

Clarification: A primary key can refer to either a single attribute or a composite key (combination of attributes). A primary key is an attribute or a group of attributes that is used to uniquely identify records.
 
Real World Example: In an Enrollment table, the StudentID and CourseID attributes form a composite key that will identify a unique enrollment. 

Importance: Composite keys let you use multiple columns as one key. This helps avoid single-column limits, cuts down data repetition, and improves design, especially in many-to-many setups like students and courses.

SQL Query

Here, {StudentID, CourseID} is a composite key, ensuring each enrollment is uniquely identified.

Keys in Database Management Systems (DBMS) are vital to organising a database and establishing integrity within the database. The basic types of keys include primary keys, foreign keys, candidate keys, super keys, alternate keys, composite keys, unique keys, and secondary keys. Each key has a specific function for data relationships and data integrity. When designing a schema, DBMS keys should be carefully chosen as they can impact data retrieval, data integrity, and a business's performance. Poor choices of DBMS keys can result in problems, including data duplication, slow queries, etc.

Ready to take your database performance to the next level? Reach out to Webandcrafts for high-level databases and optimise your keys, boost speed, and ensure long-term database efficiency.