What is a key in Database management?
A key is a unique identifier for a row in a database. Keys are necessary for a database because they form the bedrock of relational databases. With keys, rows can easily be picked and separated from others where they can be used for computations.
In a relational database, there are different types of keys. Before we proceed, we explain these types of keys with the help of the table below.
Employee Table
id | employee_id_no | LName | FName | Mobile_no | |
1 | 4890T | Philip | Margret | magie@gm.com | 393000292 |
2 | 5803M | Spencer | John | spens@ym.com | 049400343 |
3 | 0930H | Queens | Tyler | tyle@example.com | 850300949 |
Primary key
A primary key is a unique key, which will identify only a single row in a table and only that single row alone. A primary key cannot point to multiple records in a table.
-
From the
Employeetable, we simply choose theidcolumn as our primary key. This will mean that any row that it identifies is a unique entity of its own and cannot mean the same as another row in that table, even if other columns hold the same data. -
Looking at the image below, the columns marked in blue can also be chosen as the primary key. That will lead us to the next type of key.
Candidate key
A candidate key is a table column or a set of table columns that can uniquely identify a row in the table. After the primary key of choice has been taken, any other column which can uniquely identify the rows of that table is a candidate key.
- The
employee_id_no,emailandmobile_nocan serve as candidates to be used as primary keys. So, a candidate key is a unique identifier that has the potential to serve as a primary key.
Foreign key
A foreign key is a key in one relation (table) which refers to a primary key in another relation. It simply means that this key will be constrained by the primary key column in the reference table.
Normally, a foreign key constraint column cannot be deleted without some actions being initiated. Some of these actions include:
- CASCADE
- RESTRICT
- NO ACTION
- SET NULL
The Employee table can have its primary key, id, as a foreign key in say a Roster table.
Super key
A super key is a set of all unique identifiers that could possibly exist in a table. It consists of all candidate keys, and composite keys as well.
From the Employee table, the following columns can be a part of the super key set.
Super keys of the employee table
{id} |
{employee_id_no} |
{email} |
{phone} |
{id, employee_id_no} |
{email,employee_id_no} |
{phone, email} |
{email, phone,employee_id_no} |
The super key superset can contain more values than the ones indicated in the table above. The combination must uniquely identify the rows in the table.
Other keys
A composite key simply indicates that the key in question was made with the combination of more than one column. Sometimes, you may want to combine more than one column in your database table to uniquely identify a row. Both primary keys and foreign keys can be composite in nature depending on how you wish to identify your rows and the columns available.
There are also alternate keys which are all other unique keys left after the primary key has been selected from the candidate keys set.
