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 aPRIMARY KEY
. -
Some systems would only cluster a table on a
PRIMARY KEY
, hence making the composite thePRIMARY KEY
would improve performance of the queries joining on the composite.