What is a composite key?

A composite key in SQL is a primary key that consists of two or more columns. It uniquely identifies each record in a table through the combination of its constituent columns, rather than a single column. This approach is often used when no single attribute can guarantee uniqueness on its own, but a combination of attributes can.

What is a Composite Key?

In relational databases, a primary key serves to uniquely identify each row in a table. While often a single column is sufficient for this purpose, there are scenarios where a combination of multiple columns is necessary to guarantee uniqueness. This combination of columns is known as a composite key (or compound key).

The primary function of a composite key is to ensure that every record in the table can be uniquely distinguished from others. Each column within the composite key might not be unique on its own, but their combined values must be unique across all rows. This is particularly useful in many-to-many relationships and when natural identifiers are composed of multiple attributes.

Why Use Composite Keys?

• Natural Uniqueness: When the natural identifier for an entity inherently involves multiple attributes (e.g., (flight_number, flight_date) for a flight schedule, or (student_id, course_id) for an enrollment).
• Resolving Many-to-Many Relationships: Often employed in junction (or bridge) tables to link two other tables, where the combination of foreign keys from those tables forms the primary key of the junction table.
• Data Integrity: Enforces uniqueness constraints more accurately when a single column is not enough to identify a record uniquely, preventing duplicate logical entries.
• Avoiding Surrogate Keys: Allows using meaningful data columns as identifiers, rather than generating an artificial, non-meaningful surrogate key.

Example: Student Enrollments

Consider a database system for managing student course registrations. A student can enroll in multiple courses, and a course can have multiple students. To track specific enrollments, a junction table, Enrollments, is created.

In the Enrollments table, neither student_id nor course_id can be the primary key on its own. A student can enroll in many courses (student_id is not unique), and a course can have many students (course_id is not unique). However, the combination of (student_id, course_id) uniquely identifies each distinct enrollment instance, as a student can only be enrolled in a specific course once. Thus, (student_id, course_id) forms the composite primary key.

SQL DDL for Composite Key

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_title VARCHAR(100)
);

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Considerations for Composite Keys

• Index Size and Performance: Composite keys can lead to larger indexes compared to single-column keys, which might slightly increase storage requirements and potentially impact query performance for very large tables, especially with many columns in the key.
• Foreign Key Complexity: When other tables need to reference a table with a composite primary key, their foreign keys must also be composite, matching all columns of the referenced key. This can make schema design and queries slightly more complex.
• Readability and Maintenance: Queries involving composite keys often require referencing multiple columns in JOIN clauses, WHERE clauses, or GROUP BY clauses, which can make SQL statements more verbose.
• Trade-off with Surrogate Keys: Developers often weigh the benefits of natural composite keys against the simplicity of a single-column, auto-incrementing surrogate key. The choice depends on specific data modeling requirements and performance considerations.