What is a Composite Key?
Definition
A composite key is a key made of two or more columns that together uniquely identify a row, while each column alone is not sufficient.
It can be:
- a composite primary key
- a composite candidate key
- a composite UNIQUE constraint
Simple example
order_items
-----------
order_id
product_id
quantity
Neither column alone is unique:
- One order has many products
- One product appears in many orders
But together:
PRIMARY KEY (order_id, product_id)
➡️ This pair uniquely identifies a row.
Why composite keys exist (relational logic)
Composite keys naturally appear in:
- Many-to-many relationships
- Association / join tables
- Time-scoped entities
Examples:
user_id + role_idstudent_id + course_idaccount_id + date
Composite key vs Single-column key
| Aspect | Composite Key | Single-column Key |
|---|---|---|
| Columns | 2+ | 1 |
| Natural to domain | Often yes | Often surrogate |
| Foreign keys | More complex | Simple |
| ORM support | Painful | Easy |
| Index size | Larger | Smaller |
Composite PRIMARY KEY vs Composite UNIQUE
PRIMARY KEY (order_id, product_id)
Defines row identity
NOT NULL enforced on both columns
UNIQUE (order_id, product_id)
Just enforces uniqueness
NULL rules apply
ORM & real-world tradeoff ⚠️
In practice (Hibernate/JPA, Spring Data):
❌ Composite primary keys:
- Complicate entity identity
- Make references verbose
- Hurt performance in joins
✔️ Common compromise:
id BIGSERIAL PRIMARY KEY,
UNIQUE (order_id, product_id)
This keeps:
- Simple surrogate key
- Business uniqueness enforced
Senior engineers do this deliberately.
Interview pitfalls ❌
- Saying “composite key is just multiple primary keys” ❌
- Thinking order of columns doesn’t matter (it does for indexes)
- Using composite PK everywhere “for purity”
- Forgetting FK complexity
One-sentence interview answer (perfect)
A composite key is a key composed of multiple columns that together uniquely identify a row when no single column is sufficient.
Bonus follow-up (very common)
“Would you use a composite primary key in production?”
Strong answer:
Only when the composite identity is fundamental and stable; otherwise I prefer a surrogate primary key with a composite UNIQUE constraint.