When would you define a table's primary key over several columns rather than one surrogate column, and what does the ordering of columns in that key affect?

## What a composite key is A composite (compound) primary key names two or more columns. The uniqueness guarantee applies to the *combination*: no two rows may share the same tuple of values, while any single column may repeat freely. All columns are implicitly NOT NULL, as in any primary key. ## When identity really is a tuple **Link (junction) tables.** A table resolving a many-to-many relationship — say student and course — has no identity of its own beyond the pair it connects. `PRIMARY KEY (student_id, course_id)` both identifies the row and enforces the business rule that a student enrols in a course at most once. Adding a surrogate id here is not wrong, but unless you also add a unique constraint on the pair, you have deleted the rule you cared about. **Weak entities / children numbered within a parent.** An order line exists only inside its order and is naturally identified as `(order_id, line_no)`. The parent id is part of identity, not merely a reference. **Tenant-scoped identity.** In multi-tenant systems `(tenant_id, entity_id)` makes tenant scoping structural: an id from one tenant cannot accidentally match a row of another, and the leading tenant column gives locality and a natural partition/shard boundary. **Bitemporal or versioned rows.** `(entity_id, valid_from)` identifies one version of an entity. ## What column order changes A multi-column index is sorted lexicographically: first by column one, then within equal values by column two, and so on. Two consequences follow. *Access paths.* The index supports predicates that constrain a **leading prefix**. With `(tenant_id, order_no)` you get efficient seeks for `tenant_id = ?`, and for `tenant_id = ? AND order_no = ?`. A query filtering only on `order_no` cannot seek — the matching entries are scattered through the whole index — and the planner will scan, or use a different index. So put the column that is always present in predicates first. *Physical grouping.* In engines that store the table clustered by the primary key, order determines where rows physically live. `(tenant_id, order_no)` stores a tenant's rows contiguously, so "all orders for tenant X" reads a small run of pages instead of touching the whole table. Reverse the order and the same query becomes scattered I/O. In heap-organised engines the effect is weaker but still shows up in index-only scans and correlation. Selectivity folklore ("most selective column first") is a weaker guide than these two rules: choose the order that matches how the table is queried and how you want rows grouped. ## Costs to weigh - **Propagation.** Any foreign key referencing the table must repeat every key column. Grandchildren then carry the whole chain, and keys grow wider at each level. - **Index footprint.** In a clustered engine, every secondary index entry stores the full primary key as its row pointer, so a wide composite key inflates all of them. - **Tooling friction.** Many ORMs, caching layers, REST URL schemes, and audit frameworks assume a single scalar id. Composite keys are supported but often awkward. - **Mutability risk.** If a component of the key is a business value that can change, you inherit the whole problem of mutable keys — updates that must propagate to every referencing row. ## The pragmatic middle ground A very common pattern is to give the table a narrow surrogate primary key *and* a unique constraint over the natural tuple. You keep single-column references and ORM comfort while the engine still enforces "one enrolment per student per course". Reach for a true composite primary key when the tuple is also how you want rows physically organised — link tables and tenant-scoped tables are the strongest cases — and for pure link tables the composite key is usually the better default, since a surrogate id there adds a column and an index that nothing ever uses.