What Is a Composite Key in SQL?

A composite key is a primary key made up of two or more columns combined, where the combination of values (not any single column alone) uniquely identifies e...

Published November 20, 2026

A composite key is a primary (or unique) key made of two or more columns whose combination must be unique. It is common in join tables such as order_items(order_id, product_id).

The concept

Each column alone may repeat, but the pair cannot. This models many-to-many relationships naturally without a separate id.

Composite foreign keys reference composite primary keys. Many teams still prefer a surrogate id for simpler joins.

Unique by the pair

order_id product_id qty
1 10 2
1 11 1 ✓ different pair
1 10 5 ✗ duplicate pair

Practical examples

Create

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  qty INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Composite unique constraint

ALTER TABLE enrollments ADD UNIQUE (student_id, course_id);

Column order matters for indexes

-- helps WHERE order_id = ? but not WHERE product_id = ? alone

Common mistakes

  • Wrong column order for index usage.
  • Composite keys with nullable columns.
  • Using very wide composite keys everywhere.

Key takeaways

  • Uniqueness of a column combination.
  • Natural for join tables.
  • Mind index column order.

FAQ

Composite vs surrogate key?

Surrogate ids simplify references; composite keys enforce natural uniqueness.

Can a foreign key be composite?

Yes, referencing a composite key.

More SQL articles