Course contentsShow
← Data Science
Lesson 1053 of 2,14527. Database Design and OptimizationFree lesson

Composite Keys and Multi-Column Relationships

Explore primary and foreign keys that span multiple columns for complex uniqueness requirements.

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.