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.

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. This means 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.