# 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:

| Customer\_code | First\_name | Surname | Telephone\_number |
| -------------- | ----------- | ------- | ----------------- |
| 1              | Ariane      | Edison  | 448-2143          |
| 2              | Georgei     | Edison  | 448-2142          |
| 3              | Abbas       | Edison  | 9231-5312         |

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_.

<sub>_This page is licensed: CC BY-SA / Gnu FDL_</sub>

{% @marketo/form formId="4316" %}
