databases

Definition

A primary key should be a key candidate which is


Criteria

Not Null

A primary key should never be null since null is not expressive enough and too vague in the context of a table.

Unique

A primary key should be unique within a table since the primary key labels each row and each row should be unique for filtering.

Composite Keys

This also applies for composite keys. The combination of two keys, which is nothing but a tuple, should be unique for the entire table.

Minimal

A primary should be minimal, meaning that as few as possible key candidates should be used to build a primary key (or composite primary key).

Immutable (Stable)

A primary key should be immutable, meaning it is not opposed to any reason for change.

Why Immutable?

Assuming that the primary key is mutable and used in tables as foreign key, the same tables must be updated when the primary key changes such that no relations are lost and no dead links (inconsistencies) emerge.

The update complexity would be when changing the primary key, where is the number of references.