Different Types of Keys in Relational Database

A key, or index, 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.

1. Candidate Key

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.

2. Primary Key

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

3. Surrogate Key

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.

4. Foreign Keys

A relation between two tables is created by assigning a common field to the two tables. This common field must be a primary key to one table.

Foreign keys allow for referential integrity. What this means is that if a foreign key contains a value, this value refers to an existing record in the related table.

Foreign keys also allow cascading deletes and updates. Foreign keys can also contain null values, indicating that no relationship exists.