Composite Key

A composite key is a key made of two or more columns that together uniquely identify a row in a table, when no single column is unique on its own. Each column may repeat, but the combination of values never does.

Composite keys are common in junction tables that link two other tables, such as an enrollments table where (student_id, course_id) identifies each row: a student takes many courses and a course has many students, but a student enrolls in a given course only once. A composite key can be the primary key or a unique constraint, and its columns are often foreign keys to other tables.

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