Composite Key

Definition

A composite key is a key — candidate, primary, or foreign — made up of two or more columns together, where no single one of those columns is unique on its own, but the combination is.

How It Works

sql
CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id  INT NOT NULL,
  semester   VARCHAR(20) NOT NULL,
  grade      CHAR(2),
  PRIMARY KEY (student_id, course_id, semester)
);

Here, no single column is unique — many rows share the same student_id (one student takes many courses), many share the same course_id (many students take one course), and many share the same semester. But the combination (student_id, course_id, semester) is unique: a specific student cannot be enrolled in the same course in the same semester more than once. That combination is a natural composite candidate key — declared here as the composite primary key.

Edge Cases and Pitfalls

• Detecting duplicate rows against an intended but unenforced composite key is a real, common data-quality task: SELECT student_id, course_id, semester, COUNT(*) FROM enrollments GROUP BY student_id, course_id, semester HAVING COUNT(*) > 1 finds exactly the violations — useful both for auditing existing data and for verifying a proposed composite key really is unique before declaring it as a constraint.
• Column ORDER in a composite key declaration matters for indexing performance (most engines build one combined index following the declared column order, which affects which queries can use it efficiently) even though it doesn't matter for the logical uniqueness guarantee itself.
• A composite key is not automatically "more correct" than a surrogate key for the same table — many real designs use a simple surrogate id as the actual primary key while still enforcing the natural composite combination as a separate UNIQUE constraint, getting the benefits of both.

Key Takeaways

• Composite key = a key spanning multiple columns where the combination, not any single column, is what's unique.
• GROUP BY <the composite columns> HAVING COUNT(*) > 1 is the standard way to find rows that violate an intended composite key.
• A composite key can still be enforced as PRIMARY KEY, or kept as a separate UNIQUE constraint alongside a surrogate primary key.