Composite Keys and Multi-Column Relationships
What you'll learn: How to use combinations of multiple columns as primary or foreign keys when a single column can't guarantee uniqueness.
Sometimes a single column isn't enough to uniquely identify a row. A composite key (also called a compound key) uses two or more columns together to establish uniqueness. This becomes essential when modeling complex real-world relationships.
When You Need Composite Keys
Imagine a course enrollment system. A student_id alone doesn't uniquely identify an enrollment (one student takes many courses), and neither does course_id alone (many students per course). But together, the combination (student_id, course_id) uniquely identifies each enrollment.
Composite Primary Keys
Define a composite primary key by specifying multiple columns:
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id)
);
Both columns together enforce uniqueness—no student can enroll in the same course twice.
Composite Foreign Keys
Foreign keys can also span multiple columns. If a child table references a parent with a composite primary key, the foreign key must match all columns:
CREATE TABLE attendance (
student_id INTEGER,
course_id INTEGER,
attendance_date DATE,
present BOOLEAN,
FOREIGN KEY (student_id, course_id)
REFERENCES enrollments(student_id, course_id)
);
The database enforces that every attendance record matches a valid enrollment using both IDs.
Design Considerations
Use composite keys when the relationship's nature demands it, but keep them simple—queries joining on multiple columns are slightly more complex. Consider whether a synthetic single-column key (like enrollment_id) might simplify your design.
Key Takeaway: Composite keys use multiple columns together to enforce uniqueness or relationships when no single column suffices, creating precise constraints for complex data models.