Types of keys
In a relational database, keys ensure that any record in a table can be uniquely identified by one field or a combination of fields in the table.
Keys also link tables in the database together and identify relationships between them.
Primary keys
The primary key is a field which uniquely identifies each record in a table in a relational database:
- It can be pre-existing data, for example National Insurance Number
- It can be generated specifically for use in the database, eg admission number for a school student
Foreign keys
The primary key of a table may also be linked to a foreign key. This allows two tables to be linked.
Special care must be taken while inserting data and removing data from the foreign key column. A careless deletion or insertion might destroy the relationship between the two tables.
For example, a library database might be made up of three tables - books, customers and lending.
Foreign keys could be introduced to link the lending table to the books table and customers table.
- Customer ID column could exist:
- in the customers table as a primary key
- in the lending table as a foreign key
- Book ID column could exist:
- in the books table as a primary key
- in the lending table as a foreign key
Surrogate key
When an entity (table) does not have a naturally occurring primary key it is possible to create a new field that will serve as the primary key.
It is not uncommon to use an autonumber field to automatically allocate a unique number to each record in a table.
Use of autonumber usually happens when there is no other unique value that would naturally exist in a table. This is an example of making use of a surrogate key.
Composite key
A composite key is a specific type of primary key which uses the contents of two or more fields from a table to create a unique value.
Consider the number of times certain footballers scored a goal during a tournament.
| Team | Squad number | Goals |
| Aberdeen | 9 | 4 |
| Hearts | 8 | 3 |
| Celtics | 8 | 5 |
| Queen of the South | 11 | 6 |
| Aberdeen | 8 | 5 |
| Team | Aberdeen |
|---|---|
| Squad number | 9 |
| Goals | 4 |
| Team | Hearts |
|---|---|
| Squad number | 8 |
| Goals | 3 |
| Team | Celtics |
|---|---|
| Squad number | 8 |
| Goals | 5 |
| Team | Queen of the South |
|---|---|
| Squad number | 11 |
| Goals | 6 |
| Team | Aberdeen |
|---|---|
| Squad number | 8 |
| Goals | 5 |
In the above example a single field would not create a set of unique values, eg there are two Aberdeen players, and there are three players with the squad number 8.
However, if you combine the Team and Squad number fields, a primary key is created, allowing each player to be uniquely identified. This is an example of a composite key.
Compound key
A compound key is similar to a composite key in that two or more fields are needed to create a unique value. However, a compound key is created when two or more primary keys from different tables are present as foreign keys within an entity. The foreign keys are used together to uniquely identify each record.
Compound keys are always made up of two or more primary keys from other tables. In their own tables, both of these keys uniquely identify data but in the table using the compound key they are both needed to uniquely identify data.
For example, a database about school may already contain, a student table with student number as the primary key. There may be a second table for each course with a primary key called course number.
Class registers could be held in a table called enrolment, with the unique identifier for enrolment in a class being the combination of the student id and the course id.