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 = ? aloneCommon 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.