Composite Primary Keys Explained

A composite primary key is a primary key made of more than one column. The database treats the combination as the identity of the row. Each part can repeat on its own; the same combination cannot appear twice. The consequences reach foreign keys, indexes and ORM mappings.

What makes a key composite

In an order-items table, neither order_id nor product_id is unique. The pair is, because an order lists a product once. PRIMARY KEY (order_id, product_id) states that, implies NOT NULL on both columns, and creates a unique index over the pair.

Two separate UNIQUE constraints would forbid the same order from appearing twice at all, which is wrong. One composite constraint forbids only the repeated pair. The primary key version adds identity to the same rule.

When the pair or triple is the natural key

Composite keys earn their place when the combination is the thing being identified, not an accident of two foreign keys landing in one table.

• Join tables: (post_id, tag_id), (workspace_id, user_id). The pair is the membership, and duplicate membership is meaningless.
• Order lines: (order_id, line_no) when the same product may repeat with different options.
• Tenant settings: (tenant_id, key), one value per key per tenant.
• Stock levels: (warehouse_id, product_id), one quantity per product per location.
• Follow graphs: (follower_id, followee_id), which makes a duplicate follow impossible.

If one row means 'this pair', a composite key is a candidate. If one row means 'this thing, which happens to mention two others', a surrogate id usually fits better.

PostgreSQL syntax

CREATE TABLE order_items (
  order_id BIGINT NOT NULL
    REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL
    REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

PostgreSQL creates an index named order_items_pkey over the pair and marks both columns NOT NULL. A named table constraint produces clearer errors in migrations. The column order inside the parentheses is part of the declaration and affects how the index can be used.

MySQL syntax

CREATE TABLE order_items (
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

InnoDB stores rows in the clustered primary key and copies the key values into every secondary index, so wide keys cost more than they do in PostgreSQL. The key's leftmost column satisfies the order foreign key; InnoDB creates an index for product_id automatically when none exists.

Composite foreign keys

A child can reference the whole key, not one column. The columns must match in number, order and type; a foreign key cannot point at half a composite key. This is also how tenant isolation is enforced: if every child carries tenant_id in its reference, a row from one tenant cannot point at another tenant's data.

CREATE TABLE order_item_notes (
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  note TEXT NOT NULL,
  FOREIGN KEY (order_id, product_id)
    REFERENCES order_items (order_id, product_id)
    ON DELETE CASCADE
);

Indexes and column order

A composite index serves queries on a leftmost prefix. A key of (tenant_id, created_at) supports tenant_id and tenant_id plus a date range, but not created_at alone. Put the equality column first and the range column second when both patterns matter.

The key for identity and the key for queries are not always in the same order. PRIMARY KEY (order_id, product_id) answers 'what is in this order' quickly; 'which orders contain this product' needs an index beginning with product_id.

Surrogate id versus composite key

A surrogate key is one generated column, usually an identity integer or a UUID. It is narrow, stable, and painless for every ORM and foreign key. The composite key encodes the real uniqueness rule without adding a column. Both designs can be correct.

• Composite strengths: uniqueness lives in the schema, duplicates are impossible by construction, and no extra index is needed.
• Composite costs: children repeat two or three columns, and every component must be immutable or carry ON UPDATE CASCADE.
• Surrogate strengths: one narrow column in every reference, simple API identifiers, and easy key changes.
• Surrogate costs: the real rule still needs a UNIQUE constraint, and an id alone permits duplicate pairs.

A common middle ground is a surrogate primary key plus a composite UNIQUE constraint. Foreign keys stay simple, and duplicate pairs stay impossible.

How composite keys appear in ER diagrams

Every key column receives a PK badge. Two PK badges in one table mean one key made of two columns, not two independent keys. A composite foreign key draws one edge carrying both column pairs rather than two separate edges.

Join tables are the clearest case: two edges arrive from different parents, and both incoming columns carry PK badges. ERDGenerator badges each key column and labels the edge with the column pairs it carries.

Common mistakes

• Nullable components: a primary key column is forced NOT NULL. If part of the natural key can be null, the key cannot represent it; use a surrogate.
• Wide keys: three or four columns repeated across children and, in InnoDB, copied into every secondary index.
• Mutable components: if a key value can change, every referencing row must change with it. Use ON UPDATE CASCADE deliberately.
• Wrong grain: keying order_items by (order_id, product_id) forbids the same product twice. If a line can carry options, use (order_id, line_no).
• Over-scoping: adding tenant_id to a key helps only if child foreign keys carry tenant_id too.
• ORM friction: Prisma uses @@id, JPA uses @EmbeddedId or @IdClass, Rails supports composite keys since 7.1, and Django since 5.2.

Worked example: duplicate prevention

The key works at insert time. The second statement fails, and the failure is the feature.

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (42, 7, 2, 19.99);

-- ERROR: duplicate key value violates unique constraint
-- DETAIL: Key (order_id, product_id)=(42, 7) already exists.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (42, 7, 1, 19.99);

Product 7 can still appear in order 43, and order 42 can still hold product 9. Only the exact pair is rejected.

Worked example: tenant-scoped settings

Here the key is natural and also serves as the conflict target for an upsert. The same key exists once per tenant and repeats safely across tenants.

CREATE TABLE tenant_settings (
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  key TEXT NOT NULL,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, key)
);

INSERT INTO tenant_settings (tenant_id, key, value)
VALUES ('6f1c2a54-3e0b-4b7a-9d21-8f5e0c7a1b32', 'timezone', '"Europe/Paris"')
ON CONFLICT (tenant_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now();

The conflict target must name the same column set as the unique index. ON CONFLICT (key) would fail here because key alone is not unique; the database is refusing a statement that misreads the grain.

Can a composite key contain a foreign key?

Yes, and it usually does. Join tables and order lines are built from foreign keys that are also key components. A column can carry both badges: it identifies the row within the parent and references the parent's key.

Is a composite primary key the same as a unique constraint?

No. A primary key identifies the row, is limited to one per table in PostgreSQL and MySQL, forces NOT NULL, and is the default reference target. A unique constraint can exist several times per table and, in PostgreSQL, allows multiple NULLs by default. Both can enforce pair uniqueness; only the primary key is the table's identity.

Should the table also have an id column?

For a pure pair table, no. Add one when rows need to be addressed individually by URL or API, when duplicates of the natural pair are legitimate, or when other tables should reference the row with a single column. If you add it, keep the composite UNIQUE constraint; the id does not prevent duplicates.