Importance of Keys in DBMS

In database designing, keys play an important role. Keys are used for storing, accessing, and managing the data efficiently in a database. A DBMS key is essentially an attribute or a set of attributes that uniquely identifies a record or a row in a table.

Sometimes it may become a little tricky to identify a particular type of key because there are different types of keys, each with a unique role. In this chapter, we will provide an overview of the different types of DBMS keys that we come across while working on a database.

Why Do We Need Keys in DBMS?

The primary objective of having keys in a table is to provide every record with a unique identity of its own. Let's suppose we have a table that stores the details of students. This table has columns like SID (Student ID), SNAME (Student Name), SBRANCH (Student Branch), and SEMAIL (Student Email).

If we want to find the email of a student named, say, Adam, we might face an issue. This is because there could be more than one Adams in the table. Similarly, looking for a student in the CS branch also bring up multiple records.

But if we compare the situation with real-world scenarios, we assign roll numbers for students. Here in the SID column, we will notice that it has a unique value for each student. The same goes for the SEMAIL column. These attributes can uniquely identify every student in the table. Hence, SID and SEMAIL can be considered "keys" for the table.

Importance of Keys in a Database

It's important to have keys in a database for the following reasons −

• Unique Identification − Without relevant keys, we would have no way to uniquely identify a specific row in a table. We might end up retrieving multiple rows or even incorrect data.
• Data Integrity − DBMS keys ensure that every piece of data in a table is identifiable and valid. By making attributes mandatory as keys, we can prevent duplicate and incorrect entries.
• Establishing Relationships − Keys help in creating connections between different tables in a database. We call it "relations" that ensure the database maintains consistent and accurate information.

Types of Keys in DBMS

We have several different types of keys, each with a role of its own. In this section, we will provide a brief overview of the following types of keys −

• Super Key
• Candidate Key
• Primary Key
• Foreign Key
• Composite Key
• Compound Key
• Surrogate Key

In the subsequent chapters of this tutorial, we will cover each of these keys in greater detail.

Super Key

SUPER keys are the most basic type of keys. SUPER keys include all possible keys in a table. They can exist both individually and in combinations. For example, in our Student table −

• SID, Registration ID, and SEMAIL are all SUPER keys individually.
• Combinations like (SID + Registration ID), (SID + SEMAIL), or all three together (SID + Registration ID + SEMAIL) are also SUPER keys.

In simple terms, a SUPER key is any attribute (or set of attributes) which can uniquely identify a record in the table.

Candidate Key

CANDIDATE keys are a minimal subset of SUPER keys. They are the smallest group of attributes that can uniquely identify rows. There is a little difference with the SUPER keys. Unlike SUPER keys CANDIDATE keys avoid unnecessary combinations.

For example −

• SID, SEMAIL, and Registration ID can each uniquely identify a record.
• However, combinations like (SID + SEMAIL) are unnecessary since SID or SEMAIL alone can do the job. So, SID, SEMAIL, and Registration ID are CANDIDATE keys.

Primary Key

A PRIMARY key is simply one of the CANDIDATE keys chosen to uniquely identify rows in a table. It is selected by the database administrator based on practical needs.

For instance, in our Student table, SID is auto-generated, while Registration ID is more meaningful for administrative tasks. Choosing Registration ID as the PRIMARY key makes sense. It is something users and administrators can relate to directly.

All other CANDIDATE keys that are not chosen as the PRIMARY key become ALTERNATE keys.

Foreign Key

A FOREIGN key connects two tables and forms the referential integrity. It is an attribute in one table that refers to the PRIMARY key of another.

Suppose we have another table named BRANCH with details like: Branch Code and Branch Name. In our Student table, the SBRANCH column could use Branch Code as a FOREIGN key. It ensures that the branch listed for a student matches an existing branch in the BRANCH table.

If someone tries to enter a branch that does not exist, the database will throw an error. In this way, foreign keys help maintain consistency and accuracy across related tables.

Composite Key

A COMPOSITE key consists of multiple attributes that, together, uniquely identify a row. For example, (SEMAIL + SID) or (Registration ID + SEMAIL) can act as COMPOSITE keys in the student table.

Composite Keys are mainly used when a single attribute cannot identify a single row properly.

Compound Key

A COMPOUND key is a specific type of COMPOSITE key. Here, at least one attribute is a FOREIGN key. For instance, if we combine SBRANCH (FOREIGN key) and SID to identify a student, it becomes a COMPOUND key.

A Compound Key not only uniquely identifies a record but also establishes a relationship with another table.

Surrogate Key

A SURROGATE key is an artificial key created when there is no natural attribute that can act as a PRIMARY key. It adds no meaning to the data but serves the sole purpose of identification.

For example, if our Student table did not have unique attributes like SID or Registration ID, we could create a new column called Student_No and use it as a SURROGATE key.

Conclusion

In this chapter, we covered the fundamental concepts of DBMS keys. We explained why keys are required and highlighted their role in maintaining unique identification, data integrity, and relationships. We then touched upon the different types of keys that are used in DBMS.