What is a composite key? How do you define one in MySQL?

Difficulty: 5/10

primary keys, data modeling, indexing

Understanding Composite Keys in MySQL

A composite key is a primary key or unique key made up of two or more columns. Together, these columns uniquely identify each row, even if no single column can uniquely identify the row by itself.

1. Why Composite Keys Are Used

1
When a single column is not enough to uniquely identify a record.
2
To prevent duplicate combinations of related values.
3
Common in many-to-many relationship tables (junction tables).
4
Useful for enforcing natural relationships instead of artificial IDs.

2. Rules of Composite Keys

1
All columns in the composite key together must be unique.
2
None of the composite key columns can contain NULL values.
3
MySQL treats the combination of values as the primary identifier of the row.

Example: Defining a Composite Primary Key

Example: Composite UNIQUE Key

In both examples, no single column uniquely identifies a row, but the combination of the two columns does. This is the core idea of a composite key.

Scenario Questions

0-2 years experience

2-5 years experience

Follow-up Questions