What is a Composite Key?

A Composite Key is a Primary Key (or sometimes a Unique Key) that is made up of two or more columns.

It’s called composite because it’s combined from multiple columns to uniquely identify a row.

---

Why Use a Composite Key?

• Sometimes, no single column can uniquely identify a row.
• But a combination of columns together can.

✅ Composite Key ensures that no two rows have the same combination of values in these columns.

---

Example

Let’s say you have a table of course enrollments:

student_id	course_id
1	101
1	102
2	101

• student_id alone is not unique — a student can enroll in many courses.
• course_id alone is not unique — many students can enroll in the same course.
• But (student_id, course_id) together are unique — a student can’t enroll twice in the same course.

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)   -- Composite Primary Key
);

This means:

• No two rows can have the same student_id and course_id together.
• Combination must be unique.

---

Composite Key vs Single Column Key

Single Column Key	Composite Key
One column uniquely identifies the row.	Combination of multiple columns uniquely identifies the row.
Simple PRIMARY KEY (id)	Composite PRIMARY KEY (col1, col2, ...)
Example: user_id	Example: student_id + course_id

---

Real-Life Analogy

Think of:

• First Name = not unique.
• Last Name = not unique.
• First Name + Last Name = more unique, but still not perfect.
• First Name + Last Name + Date of Birth = very likely unique — a composite key!

---

Important Rules About Composite Keys

• All columns together must be NOT NULL.
• All columns are treated as one unit for uniqueness.
• You can still have other UNIQUE constraints on other columns.

---

In Short

A Composite Key is a Primary Key or Unique Constraint made up of multiple columns that together uniquely identify a row.