What is are database keys? 

A database key can be defined as an attribute or a collection of attributes that are used to uniquely identify rows (tuples/records) in a relation (table). In relational databases, keys are useful to uniquely determine the relationships between different tables.

In this article, you will learn the following.

• What are database keys? 
• Why are keys important in databases? 
• The types of keys in a database management system. 
• What is a super key? 
• What is a candidate key? 
• What is a composite key? 
• What is a primary key? 
• What is a foreign key? 
• What is a surrogate key? 

Why are keys important in databases?

• Keys ensure that each record in a table is clearly identified. Accordingly, this prevents one from having duplicate data in a database table, which causes data redundancy, and ultimately leads to database anomalies in one's database design.
• In a relational database, keys are used to define and establish the relationships between tables, and they are also useful to uniquely identify a record/row in a table.
• Keys also enforce data integrity in the relation.

The types of keys in a database management system.

• Super Key - In a relational database, a Super Key consists of a set of attributes, that uniquely identify any row in a relation.

Here's an example using a Student Table.

The super keys are listed as follows.

• {Student_id}
• {Student_Phone}
• Student_id, Student_Name}
• {Student_id, Student_Phone}
• {Student_Phone, Student_Name}
• {Student_id, Student_Name, Student_Phone}

Note well - the Student_Name attribute cannot be assigned as a super key because we cannot guarantee a unique value. For instance,  the name Abi can have multiple occurrences in the Student Table. However, we see the Student_Name attribute being included to form the composite key along with Student_id or Student_Phone or both. 

• Candidate Key - A minimal collection of attributes that allows you to uniquely identify a given row.  In essence, a candidate key is a super key where no redundant/unnecessary attributes are included. In addition, the candidate key offers diverse options to the database designer to select one primary key. Another feature of the candidate key is that it does not contain null attributes, as wells as it may not have multiple attributes.

Note well as mentioned in this video.

• Each candidate key can effectively function as a primary key but it is imperative that the database designer selects only one primary key.
• A primary key is always a candidate key as well as a super key but each candidate key may not be a primary key.
• Each candidate key is a super key but each super key may not be a candidate key.

Revisiting our Student Table example.

Both Student_id and Student_Phone are candidate keys, which will help us to uniquely identify the student record in the table.

• Composite Key - is a key that consists of multiple attributes (table columns) that can be used to uniquely identify each row in a table.  Note that the attributes when taken independently/individually do not guarantee uniqueness.

Going back to our Student Table, the following attributes are composite keys.

• Student_id, Student_Name}
• {Student_id, Student_Phone}
• {Student_Phone, Student_Name}
• {Student_id, Student_Name, Student_Phone}

• Primary Key - is a specific choice of a minimal set of attributes (table column) that uniquely identifies any data row in a table. In essence, the primary key is selected from one of the candidate keys and this key becomes the “identifying key” of a table. Unique properties of primary keys include the following. 
  • Its value is not null.
  • It has a unique value for each row.                

Revisiting our Student Table example.

In this example, both Student_id and Student Phone can be set as the primary keys. However, as a database designer, it would be more logical to set Student_id as the primary key.

• Foreign Key - the overarching goal of the foreign key is to create and define relationships between tables. In addition, they help to maintain data and referential integrity in a database. Basically, when the primary key of one table is included as a non-unique attribute of another table, then such an occurrence is referred to as a foreign key. 

Let's consider the following example using the Student Table,  Course Table, and Instructor Tables

In the Student Table - The Primary key is Student_id, while the Foreign Key is Course_id.

In the Course Table - The Primary key is Course_id, while the Foreign Key is Instructor_id.

In the Instructor Table - The Primary key is Instructor_id, and there are no Foreign Keys.

As mentioned earlier, foreign keys maintain referential identity. So now you might be wondering what exactly is referential integrity.

In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist. - Wikipedia

Therefore, referential integrity ensures that every foreign key in a child table references a valid and existing primary key in the parent table, which ultimately leads to the accuracy and consistency of the data. 

• Natural Key - Before we dive into Surrogate keys we need to understand the notion of natural keys. A natural key is a unique key in a database consisting of attributes that already exist in a dataset and are leveraged by the external world outside the database. It's main features include the following.
  • It is based on real-world data.
  • It promotes faster implementation
  • It is meaningful
• In the case of the Course Table, the natural primary key would be Course_id, which would be a unique identifier for all course records in a table.

• Surrogate key - This is a synthetic key, which aspires to uniquely identify each record. It is usually created when a natural primary key is missing from the database.  Typically, Surrogate keys in DBMS are numeric, meaningless, autogenerated, and have an increasing sequential order. 

Note well that there are instances where the natural primary key cannot be integrated with other records due to incompatibility of the data.

Considering the Course Table for the Computer Science Department the Course ids (Course_id) are displayed as alphanumeric characters but what happens if we were to add courses from the Mathematics Department, which only contains numeric characters? Then, we would need a surrogate key. 

With the help of surrogate keys, we are now able to avoid incompatibility in our datasets and support better integration of the data.

Summary

In this article, we have explored the following.

What is a key in DBMS? - A key is an attribute or a collection of attributes that are used to uniquely identify rows (tuples/records) in a relation (table). 

Why are keys important in databases?

• Keys prevent duplicate data and reduce anomalies in your DBMS.
• Keys define and establish the relationships between tables.
• Keys are handy to uniquely identify a record/row in a table.
• Keys also enforce data integrity in the relation.

In addition, we have explored with examples, the notion of super keys, candidate keys, composite keys, primary keys, foreign keys, and surrogate keys. 

If you have been empowered by the information shared in this article, then please clap using the clap emoticon. Please feel free to ask any questions or to share your comments using the comment emoticon.

FOLLOW Kadian:

Instagram: https://www.instagram.com/kadiandavisowusu/

Linktree: https://linktr.ee/kadiandavisowusu