Keys

Introduction
A key is a field or set of fields whose values uniquely identify a record.

Candidate key
Any field or set of fields which can be considered as a key is known as a [|candidate key].

In this example, both Employee Number and Email are candidate keys, because both can uniquely identify the record in the table

Primary key
The [|primary key] is the candidate key which is chosen to be used.

It is better to avoid a meaningful primary key. In the example above, it's possible that the company may change the structure of staff email addresses. Given this, it would be better to use Employee Number as a primary key as this is less likely to change.

A primary key consisting of only one field is known as an atomic key.

Compound key
It is possible in some tables that no one column can act as a primary key, and that a combination of columns are required to create the key. This is known as a compound key.

In this example, Order ID cannot be the primary key because it is not unique. Similarly, Product ID cannot be the primary key because it is not unique.

Instead, the primary key would be a compound key of Order ID and Product ID because this is unique for each record.

Surrogate key
A surrogate key is a special key which has no meaning or purpose other than to uniquely identify each record. Surrogate keys are often a combination of letters and numbers. A new column is created to be the primary key.

In this example, KTV have been interviewing passers-by for the evening news bulletin. The production team take a note of the interviewee's name and the duration of the interview. As these people are not related to KTV in anyway, a surrogate key must be created in order to uniquely identify them.

Foreign key
A [|foreign key] is a column which is added to one table to create a relationship with another table.



In this example, the table Teachers stores data about teachers in a school. The second table Departments stores data about departments in a school.

The table Teachers has a foreign key Dept Code which is used to create a link to the primary key in the table Departments.

From this, we can see that David McCall works in the English department, and both Elaine Brunton and Sara McQueen work in the Computing department.

Scholar link : [|Keys]