Composite Key in Database Design
Fast recall
Quick revision
Core answer
(student_id, course_id) when one enrollment per student-course pair is the business rule.Understanding when multiple columns must combine to guarantee uniqueness and how composite keys are used in many-to-many relationships
Adding a surrogate id but forgetting to keep a UNIQUE rule on the natural foreign-key pair
Discuss the trade-off between composite keys and surrogate keys in junction tables, including query complexity versus foreign key simplicity when other tables need to reference the junction table
Interview-ready guide
Interview answer
A composite key uses two or more columns together to identify one row.
For example, (student_id, course_id) can identify one Enrollment when the business allows one enrollment for each student-course pair. Neither column is unique alone. The pair can be declared as the primary key.
Another valid design adds a surrogate enrollment id and keeps a UNIQUE constraint on the pair, especially when other tables need a simple foreign key to one enrollment. In JPA, a composite primary key can use @EmbeddedId or @IdClass. The key class needs the equality, hash, serializability, and constructor rules required by JPA and the selected provider.
Column order also affects the primary-key index, so additional indexes may still be needed for other query patterns. The important point is that the key must express the real uniqueness rule rather than a habit about junction tables.
Composite Key in Database Design
Direct Answer
A composite key uses two or more columns together to identify a row. It can be a candidate key, the chosen primary key, or a referenced key. It is common in junction tables, such as (student_id, course_id) when one enrollment per student-course pair is the business rule.
Code Example
import jakarta.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Objects;
@Embeddable
public class OrderItemId implements Serializable {
private Long orderId;
private Long productId;
protected OrderItemId() {}
public OrderItemId(Long orderId, Long productId) {
this.orderId = orderId;
this.productId = productId;
}
@Override public boolean equals(Object other) {
if (this == other) return true;
if (!(other instanceof OrderItemId that)) return false;
return Objects.equals(orderId, that.orderId)
&& Objects.equals(productId, that.productId);
}
@Override public int hashCode() {
return Objects.hash(orderId, productId);
}
}
@Entity
@Table(name = "order_items")
public class OrderItem {
@EmbeddedId
private OrderItemId id;
@ManyToOne @MapsId("orderId")
@JoinColumn(name = "order_id")
private Order order;
@ManyToOne @MapsId("productId")
@JoinColumn(name = "product_id")
private Product product;
private Integer quantity;
private BigDecimal priceAtPurchase;
}
This key assumes one line per product in an order. If the domain permits repeated product lines, add a line number or use a surrogate line id and keep the required business uniqueness separately.
Common Mistakes & Pitfalls
- Losing natural uniqueness: if a junction uses a surrogate id, add a UNIQUE constraint on the foreign-key pair when duplicate links are forbidden.
- Assuming one column order fits every query: a composite primary-key index starts with its leading column; other access paths may need another index.
- Incomplete JPA key class: an embedded id needs stable equality and hash behavior, serializability, and the constructor/access required by the chosen JPA version and provider.
- Assuming one row per product:
(order_id, product_id)is valid only if the business forbids repeated product lines.
Check your understanding
Answer these without looking back at the article.
- When would you use a composite key instead of adding a surrogate id?
- How do you map a composite key in JPA?
- What is a junction table and when do you need one?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.