Composite Keys
A composite key is a key made of two or more columns, where the combination is unique even though no single column is unique on its own. It can serve as a primary key or as a unique constraint.
Composite keys appear naturally in a few places:
- Junction tables where the pair of foreign keys uniquely names each pairing, such as a student id plus a course id.
- Weak entities whose identity depends on a parent, such as a line item identified by an order id plus a line number.
- Natural multi column uniqueness, such as a country code plus a postal code.
A key property is uniqueness of the combination, not the parts. The same student id repeats across many enrollment rows, and the same course id repeats too, but the pair appears only once.
Column order in a composite key matters for the underlying index. A composite index on order id then line number can serve queries that filter by order id alone, because the leading column is usable, but it cannot efficiently serve queries that filter only by line number. Put the most selective or most frequently filtered column first.
The tradeoff is that composite keys propagate. Every foreign key referencing the table must repeat all the columns, which can make child tables wider. Teams sometimes add a surrogate key instead to keep references narrow.
Key idea
A composite key makes a combination of columns unique, and the leading column order determines which queries its index can accelerate.