What is a composite key and when do you need one?
SHORT ANSWER
A composite key is a key made of two or more columns that together identify a row. It is needed when no single column is unique, such as a student and subject pair in a marks table.
Explain it simply
In a marks register, "Asha" appears many times, and "Maths" appears many times. But "Asha in Maths" appears once. The pair is the identity, so the key needs both columns.
Example
sql
CREATE TABLE marks (
roll_no INT,
subject_id INT,
score INT,
PRIMARY KEY (roll_no, subject_id),
FOREIGN KEY (roll_no) REFERENCES students(roll_no),
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);- The pair (1, 101) can appear only once, so one score per student per subject.
- Each column alone may repeat.
- It is typical for junction tables in many-to-many relationships, such as a student enrolled in many courses.
Notes:
- Column order in the key matters for the index. (roll_no, subject_id) speeds up lookups by roll_no, not by subject_id alone.
- Many teams still add a surrogate
idcolumn for simpler foreign keys, with a UNIQUE constraint on the pair. - No part of a composite primary key can be NULL.
What to say in the interview
"A composite key uses multiple columns together for uniqueness. It is natural for junction tables. I mention that each part is NOT NULL and that column order affects the index."
Common mistakes
- Making each column of the pair individually unique, which is a different rule.