When Do You Need a Composite Key?

When the identity of a row genuinely consists of more than one attribute, and neither attribute identifies it on its own. A booking is a room plus a night. A line item is an order plus a line number. A link between two entities is one of each. In all three cases the pair is the smallest set of columns that determines the row, and a single-column surrogate key would be inventing an identity the data does not have.

The test for whether a composite key is correct is functional dependency. Ask whether one column of the candidate key determines the whole row on its own. If it does, that column should be the key and the rest should be ordinary columns. If removing any one column makes the key unable to identify a row, the composite key is minimal and correct.

The other situation worth naming is a natural key that is too wide. If the business identifier is a three-column combination of region, code and effective date, you can either make that the primary key or add a surrogate key and enforce the combination with a UNIQUE constraint. Both are correct; the choice is about how wide your foreign keys become and how stable the business identifier is.

The argument for composite keys is honest and worth making in an interview: they express the real structure of the data, they prevent a meaningless surrogate id from leaking into every downstream system, and they make accidental duplication impossible at the schema level. The argument against is equally real: every table that references this one now carries a multi-column foreign key, joins get wider, and changing the key later is expensive.

Declare the key columns inside the table definition and add a composite UNIQUE constraint on a second pair that allows NULLs, so you can see the difference in one schema.

CREATE TABLE bookings (
  room_id INTEGER NOT NULL,
  night TEXT NOT NULL,
  guest_email VARCHAR(200),
  note VARCHAR(200),
  PRIMARY KEY (room_id, night),
  UNIQUE (guest_email, night)
);

INSERT INTO bookings (room_id, night, guest_email) VALUES
  (1, '2026-03-01', '[email protected]'),
  (1, '2026-03-02', '[email protected]'),
  (2, '2026-03-01', NULL),
  (3, '2026-03-01', NULL);

SELECT room_id, night, guest_email FROM bookings ORDER BY room_id, night;

Four rows come back. Ada has bookings on two different nights, and the two rows with a NULL guest_email are both present despite the UNIQUE constraint on (guest_email, night) — because SQL treats NULLs as distinct from each other in a unique constraint.

That last point is the detail that catches people out, and it is the difference between a composite primary key and a composite unique constraint. Every column in a PRIMARY KEY is implicitly NOT NULL, so a booking without a room or without a night cannot exist. A composite UNIQUE constraint, by contrast, allows NULLs, and multiple rows with NULL in the constrained column are permitted. If you want the guest-email rule to apply only to bookings that actually have a guest email, the constraint above is exactly right.

Also note what the second UNIQUE constraint is doing: it prevents the same guest booking the same room on the same night twice. That is a different business rule from the primary key's, and expressing both in the schema means neither can be violated by a bug in application code.

The behaviour differs between a composite primary key and a composite unique constraint, and the difference is entirely about NULL. Let the two side by side.

CREATE TABLE readings (
  sensor_id INTEGER NOT NULL,
  reading_at TEXT NOT NULL,
  value REAL,
  PRIMARY KEY (sensor_id, reading_at)
);

CREATE TABLE samples (
  sensor_id INTEGER NOT NULL,
  sample_at TEXT,
  value REAL,
  UNIQUE (sensor_id, sample_at)
);

-- A primary key column can never be NULL: every column in the key is implicitly NOT NULL.
INSERT INTO readings (sensor_id, reading_at, value) VALUES (1, '2026-03-01 08:00', 21.5);

-- A unique constraint allows NULLs, and two NULLs do not conflict with each other.
INSERT INTO samples (sensor_id, sample_at, value) VALUES
  (1, '2026-03-01 08:00', 21.5),
  (1, NULL, 19.0),
  (1, NULL, 22.0);

SELECT (SELECT COUNT(*) FROM samples) AS sample_rows,
       (SELECT COUNT(*) FROM samples WHERE sample_at IS NULL) AS rows_with_null_time;

The counts come back as three sample rows, of which two have a NULL sample time. Both of those NULL rows were accepted, even though the table has a UNIQUE constraint on (sensor_id, sample_at) — because in SQL a unique constraint treats each NULL as distinct from every other NULL.

This is a well-known SQL quirk rather than a bug, and it follows from the same three-valued logic that makes NULL = NULL unknown. Two unknowns are not known to be equal, so the uniqueness rule does not fire.

Why it matters in practice: if your business rule is "a sensor may only have one reading at a given time, and readings with an unknown time are still only allowed one per sensor", the UNIQUE constraint does not give you that. You need a NOT NULL declaration, or an expression index on COALESCE(sample_at, '') in PostgreSQL, or a partial unique index in SQLite and PostgreSQL that only covers the rows where the column is not null.

And the asymmetry with the primary key is exactly what you would hope for. A row whose key contains a NULL has no identity — you could not point at it, could not join to it, could not address it — so the primary key refuses it outright. A unique constraint is a different kind of statement: it is a rule about values, and a missing value simply falls outside the rule.