Relational Databases: Table Keys

A key, or index, as the term itself indicates, unlocks access to the tables. If you know the key, you know how to identify specific records and the relationships between the tables.

Each key consists of one or more fields, or field prefix. The order of columns in an index is significant. Each key has a name.

A candidate key is a field, or combination of fields, that uniquely identifies a record. It cannot contain a null value, and its value must be unique. (With duplicates, you would no longer be identifying a unique record).

A primary key (PK) is a candidate key that has been designated to identify unique records in the table throughout the database structure.

A surrogate key is a primary key that contains unique values automatically generated by the database system - usually, integer numbers. A surrogate key has no meaning, except uniquely identifying a record. This is the most common type of primary key.

For example, see the following table:

At first glance, there are two possible candidate keys for this table. Either customer_code or a combination of first_name, surname and telephone_number would suffice. It is always better to choose the candidate key with the least number of fields for the primary key, so you would choose customer_code in this example (note that it is a surrogate key). Upon reflection, there is also the possibility of the second combination not being unique. The combination of first_name, surname and telephone_number could in theory be duplicated, such as where a father has a son of the same name who is contactable at the same telephone number. This system would have to expressly exclude this possibility for these three fields to be considered for the status of primary key.

There may be many Ariane Edisons, but you avoid confusion by assigning each a unique number. Once a primary key has been created, the remaining candidates are labeled as alternate keys.