Should every table in a database have an identity field to use as a PK?

sql

best-practices

data-integrity

database-design

byAlex Kataev·Dec 1, 2024

Implementing an identity field as a primary key (PK) is generally recommended to guarantee record uniqueness and decouple primary keys from business data. This approach commonly employs a system-generated surrogate key like so:

CREATE TABLE Customers (
    CustomerID INT IDENTITY PRIMARY KEY,
    ...
);

This technique ensures consistency and efficiency in handling data across various tables. Nonetheless, context matters; careful consideration should precede deciding the PK strategy.

PK strategies: One size does not fit all

Notably, an identity field as PK is not always the optimal choice; sometimes natural keys or composite keys are equally viable or superior options.

Natural keys: elegance in simplicity

Natural keys are ideal when the data has inherent unique and stable attributes. Famed for their simplicity, natural keys directly represent the entity without the need for surrogate identifiers.

Composite keys: power in combinations

Composite keys, typically used when a combo of fields naturally and uniquely identifies a record, can be a lifesaver in maintaining data integrity.

Surrogate keys: a safe bet

A common practice is to employ an IDENTITY column as a surrogate key, if not necessarily as the primary key. The benefits lie in their disconnect from business data, aiding in retrieval efficiency and equalizing key length.

Identity fields: It's complicated

Contrary to expectations, identity fields do not assure uniqueness by default, rendering this additional ENFORCEMENT crucial:

ALTER TABLE Customers ADD UNIQUE (CustomerID);
-- Now that's what I call an identity crisis prevention technique!

Many-to-many: the exception to the rule

When it comes to linking tables in scenarios of many-to-many relationships, you might not require an additional identity field. Instead, you can define a composite primary key using the foreign keys from both referenced tables.

Factors to consider when choosing PK strategy

Seemingly trivial, creating identity columns should be driven by thoughtfulness. Let's walk through some considerations before adopting identity fields as PKs.

Surrogate keys: a heaven-sent solution

Surrogate keys, more than often, come to rescue especially in the realm of object-oriented development and complex databases. These system-generated identifiers provide a buffer from the real-world entities, allowing for more alterations and scaling.

Indexing and storage overhead: an often overlooked factor

In larger systems, the storage and indexing overhead of identity columns in terms of storage footprint and performance cannot be ignored. Furthermore, composite keys can aid in directly joining parent and grandparent tables, reducing the need for additional link tables.

Passion for global uniqueness: GUID/UUIDs

Ever dreamt of system-wide uniqueness for your records without breaking a sweat on overhead coordination? GUID or UUID fields make this wish come true at the cost of a little more storage space.

CREATE TABLE DistributedRecords (
    RecordID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ...
);
-- In a world where snowflakes aren't unique enough, UUIDs save the day.

The right PK strategy: a decision matrix

The perfect PK strategy is circumstantial. Below is a decision matrix to help you decide:

Table Type	PK Type	Reason
Generic Entity	Identity	Simplicity, Reference ease, Data abstraction
Linking Table	Composite	Natural keys from linked tables
Distributed	GUID/UUID	System-wide Uniqueness
Small/Legacy	Natural	Overhead may not justify identity field
Complex/Aggregated	Compound	Represents intrinsic entity qualities