Why are composite primary keys still around?

Composite keys are required when your primary keys are non-surrogate and inherently, um, composite, that is, breakable into several non-related parts.

Some real-world examples:

• Many-to-many link tables, in which the primary keys are composed of the keys of the entities related.

• Multi-tenant applications when tenant_id is a part of primary key of each entity and the entities are only linkable within the same tenant (constrained by a foreign key).

• Applications processing third-party data (with already provided primary keys)

Note that logically, all this can be achieved using a UNIQUE constraint (additional to a surrogate PRIMARY KEY).

However, there are some implementation specific things:

• Some systems won’t let a FOREIGN KEY refer to anything that is not a PRIMARY KEY.

• Some systems would only cluster a table on a PRIMARY KEY, hence making the composite the PRIMARY KEY would improve performance of the queries joining on the composite.