Composite key

What is a composite key?

A composite key uses two or more columns together to identify a row. No single column is unique on its own, but the combination is. It is the multi-column version of an ordinary primary key or unique constraint.

The rule holds in every relational database: values can repeat within any one column, but each full combination of the key columns must be unique. Uniqueness lives in the set of columns, not in any single member.

The junction table case

The most common home for a composite key is a junction table, also called a bridge table, which records a many-to-many relationship. An order can contain many products, and a product can appear on many orders, so the link gets a table of its own.

CREATE TABLE order_items (
  order_id   integer NOT NULL,
  product_id integer NOT NULL,
  quantity   integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The primary key on (order_id, product_id) lets order 10 sit on many rows and product 55 sit on many rows, while it blocks the same product from being listed twice on the same order. The business rule now lives in the schema, and splitting a many-to-many into its own table like this is what database normalisation asks for.

Column order and the backing index

A composite key is backed by a single multi-column index, and the order of the columns decides which queries it can speed up. A database reads such an index from left to right, so it can use any leftmost prefix of the columns but not an arbitrary subset.

Say the key is (customer_id, order_date). A lookup on customer_id alone uses the index, and so does a lookup on both columns together. A lookup on order_date alone cannot, because it is not a leftmost prefix. Put the column you filter on most often first.

Composite key versus surrogate key

A composite key is built from columns that already carry business meaning, close to what a candidate key or business key describes. A surrogate key is the opposite, a single invented column, usually an auto-incrementing integer or a UUID, that stands in for the whole combination.

The trade-off appears the moment other tables point at the row. A foreign key has to repeat every column of the key it references, matched by number and type, so a three-column composite key becomes three columns in every child table and three conditions in every join. A surrogate key collapses that to one column and one join.

So the wider the composite key, or the more tables that reference it, the more a surrogate key earns its place. A data warehouse often keeps both: a surrogate key for joins, plus a unique constraint on the natural combination so duplicate rows still cannot slip in.

What to watch out for with composite keys

• Member columns should be stable. The combination is the row's identity, so if one column can change, the identity changes with it, and every foreign key pointing at the row has to change too.

• Nulls behave differently per constraint. A primary key forbids nulls in all of its columns. A unique constraint allows them, and because two nulls are not treated as equal, it can let through rows you expected it to block.