What is a Composite Key?
Understand composite keys in SQL databases, when to use them, and how they resolve many-to-many relationships in junction tables.
Expected Interview Answer
A composite key is a primary key made up of two or more columns that, together, uniquely identify a row, even though no single column among them is unique on its own.
Composite keys are used when a single attribute cannot guarantee uniqueness but a combination of attributes can, such as StudentID plus CourseID in an enrollment table, since a student can enroll in many courses and a course can have many students, but each specific pairing is unique. The database enforces uniqueness across the combined columns and often uses the same combination as a foreign key reference from other tables. Composite keys are common in junction (many-to-many) tables that resolve relationships between two entities.
- Enforces uniqueness across a natural combination of attributes
- Avoids introducing an artificial surrogate key when not needed
- Models many-to-many relationships cleanly via junction tables
- Keeps referential integrity tied to meaningful business columns
AI Mentor Explanation
No single column identifies a unique row in a ball-by-ball table — MatchID alone repeats across many balls, and BallNumber alone repeats across many matches. But MatchID plus InningsNumber plus BallNumber together always points to exactly one delivery. That combination is a composite key: individually weak, but jointly unique, just like a seat is only pinpointed by combining stand, row, and seat number together.
Step-by-Step Explanation
Step 1
Identify weak individual columns
Notice that no single column guarantees row uniqueness.
Step 2
Find the combining columns
Determine which set of columns together is always unique.
Step 3
Declare the composite primary key
Add PRIMARY KEY (col1, col2) in the table definition.
Step 4
Reference it as a foreign key
Use the same combination in child tables when referencing this row.
What Interviewer Expects
- Understanding that composite keys use multiple columns together
- A many-to-many / junction table example
- Awareness of composite foreign key references
- Distinction from a single-column surrogate key
Common Mistakes
- Confusing composite key with candidate key
- Thinking each column in a composite key must be unique alone
- Forgetting composite keys are common in junction tables
- Not knowing how to declare one in SQL syntax
Best Answer (HR Friendly)
“A composite key is a primary key formed by combining two or more columns because no single column is unique on its own. A common example is an enrollment table where StudentID and CourseID together uniquely identify a row, even though each ID repeats individually.”
Code Example
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
EnrollDate DATE,
PRIMARY KEY (StudentID, CourseID)
);Follow-up Questions
- How does a composite key differ from a candidate key?
- Can a composite key also be used as a foreign key?
- What are the performance implications of indexing a composite key?
- When would you prefer a surrogate key over a composite key?
MCQ Practice
1. A composite key is formed by?
A composite key combines two or more columns to achieve uniqueness that no single column provides alone.
2. Composite keys are most commonly needed in?
Junction tables resolving many-to-many relationships typically rely on a composite key of the two related IDs.
3. In SQL, a composite primary key is declared with?
PRIMARY KEY (col1, col2) declares a composite primary key across multiple columns.
Flash Cards
Composite key — A primary key made of two or more columns combined for uniqueness.
Typical use case — Junction tables resolving many-to-many relationships.
Example — Enrollment table: (StudentID, CourseID) together.
Declaration syntax — PRIMARY KEY (col1, col2)