Composite Key in MySQL

What is a composite key

A composite key is a primary key made of two or more columns together. It is used when no single column uniquely identifies a row, but the combination does. For example, one student can enrol in many courses, so neither student_id nor course_id alone is unique — but together they are.

Why you need one

Consider an enrolments table linking students to courses. The same student appears in many rows (many courses), and the same course appears in many rows (many students). No single column is unique. But the pair (student_id, course_id) should be unique — a student should not enrol in the same course twice.

Creating a composite key

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

Listing both columns in one PRIMARY KEY (...) clause makes them a composite key. The combination must be unique, though each column alone may repeat.

How it enforces uniqueness

INSERT INTO enrollments VALUES (1, 101, 'A');   -- ok
INSERT INTO enrollments VALUES (1, 102, 'B');   -- ok (same student, new course)
INSERT INTO enrollments VALUES (1, 101, 'C');   -- ERROR: duplicate pair

Student 1 can take courses 101 and 102, but cannot enrol in course 101 twice — the composite key blocks the duplicate combination.

With foreign keys

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

In a link table, each part of the composite key is often also a foreign key. Here the two columns reference the students and courses tables, connecting them while keeping each pair unique.

Composite vs single key

Common mistakes

• Expecting single columns to be unique. Only the combination is unique.
• Using two PRIMARY KEY clauses. List all columns in one clause instead.
• Order confusion. The columns form one key; their order can affect indexing.
• Using a composite key when a single id would do. Only use it when needed.

Summary

• A composite key is a primary key of two or more columns.
• Use it when only the combination of columns is unique.
• Create it with one PRIMARY KEY (col1, col2) clause.
• Individual columns may repeat if the whole combination does not.
• It is common in link tables, where columns are also foreign keys.

Next, learn all key types in keys in DBMS explained, or auto ids in AUTO_INCREMENT.