SQL Topics
Composite Keys
title: Composite Keys
A composite key uses two or more columns together to uniquely identify a row.
When no single column provides enough uniqueness, a composite key ensures the combined values remain distinct.
Composite Primary Key
Use a composite primary key when a single column is insufficient.
Example: An enrollment system where a student can enroll in a course only once.
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID)
);StudentID can appear in multiple courses, and CourseID can appear in many students. The pair (StudentID, CourseID) must be unique.
Composite UNIQUE Constraint
You can also use a composite UNIQUE constraint when you need distinctness but not row identification.
CREATE TABLE ExamResults (
ExamID INT,
StudentID INT,
Subject VARCHAR(50),
Score INT,
UNIQUE (ExamID, StudentID, Subject)
);A student can take multiple exams, but not the same subject twice in the same exam.
Defining Composite Keys
During table creation:
PRIMARY KEY (StudentID, CourseID)After creation:
ALTER TABLE Enrollments
ADD PRIMARY KEY (StudentID, CourseID);Frequently Asked Questions
Q: Does column order matter in a composite key? A: Yes, for index and query optimization. Place the most selective or commonly filtered column first.
Q: Can a composite key include a foreign key? A: Yes. Junction tables commonly use foreign keys as part of a composite primary key.
Q: How many columns can a composite key have? A: As many as needed, but keep it minimal. Large composite keys increase index size and slow joins.
Summary
- Use composite keys when a single column is not enough for uniqueness.
- Composite primary keys identify rows.
- Composite unique keys prevent duplicate combinations without identifying the row.
- Keep the column count small and the order logical.
Next Step
See NOT NULL to learn how to require values before building more complex schemas, or return to the SQL Constraints overview.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Composite Keys.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, constraints, composite
Related SQL Topics