Detailed explanation
A composite key is a key made from two or more columns that together uniquely identify a row. When this combination is chosen as the table’s primary key, it is called a composite primary key. The individual columns may contain repeated values, but their combined values must be unique. More precisely, a candidate key should be minimal: every included column should be necessary for uniqueness. Composite keys are useful when a row represents a relationship or a record whose identity naturally depends on multiple attributes.
Consider an enrollment table that records which students take which courses: CREATE TABLE enrollment (student_id INT, course_id INT, PRIMARY KEY (student_id, course_id)); A student can enroll in several courses, so student_id alone is not unique. A course can have many students, so course_id alone is not unique either. Together, however, they identify one enrollment. Rows (101, 10), (101, 20), and (102, 10) are valid, but inserting another (101, 10) violates the primary key constraint. Because both columns belong to the primary key, neither can contain NULL.
The database checks the complete combination when enforcing uniqueness, typically using a unique index that supports the primary key constraint. Column order does not change which combinations are unique, but it can affect query performance. For a typical B-tree index on (student_id, course_id), searches by student_id, or by both columns, are generally well supported. Searches only by course_id may benefit from a separate index. Other tables can reference this composite primary key through a foreign key containing the corresponding two columns.
In an interview, explain both the uniqueness rule and the business rule it represents. This design permits only one enrollment per student-course pair; if repeat enrollments across semesters are allowed, the key needs another attribute, such as semester_id, or a different design. Also distinguish a composite key from multiple primary keys: a table has only one primary key, although that key can contain multiple columns. A generated enrollment_id is an alternative primary key, but a UNIQUE constraint on the appropriate business columns is still needed to prevent duplicate enrollments.
Key points to remember
- A composite key combines two or more columns to identify a row uniquely.
- Uniqueness applies to the combination, not to each column separately.
- Every column in a composite primary key is NOT NULL.
- A table has one primary key, which may contain several columns.
- Key columns must reflect the business rule; index column order can affect query performance.
Common mistakes candidates make
- Saying that every column in a composite key must be individually unique.
- Describing a composite primary key as several separate primary keys.
- Adding a generated ID and assuming it automatically prevents duplicate student-course pairs.
Likely follow-up questions
Is a composite key always a primary key?
No. A composite candidate key can be selected as the primary key or remain an alternate key, commonly enforced with UNIQUE and NOT NULL constraints.
Can a foreign key reference a composite primary key?
Yes. The foreign key must include the corresponding columns with compatible types, for example FOREIGN KEY (student_id, course_id) REFERENCES enrollment(student_id, course_id).
When would you use a generated ID instead?
A generated ID can simplify references when the natural composite key is wide or likely to change. Keep a UNIQUE constraint on the business-column combination if duplicate combinations must still be prohibited.
Interview tip: Define combined uniqueness first, then demonstrate it with one valid repeated student ID and one rejected duplicate student-course pair.