Composite Primary Keys in MySQL

What is a Composite Primary Key?

A composite primary key, also referred to as a compound key or concatenated key, is a type of primary key that consists of two or more columns in a database table. These columns, when taken together, ensure the uniqueness of each row in the table.

Consider the following example, where each team can have multiple individuals as members. Each person can be a member of multiple teams, but they can only be a member of the same team once.

In this scenario, the person with person_id 100 is a member of both team 1 and team 2. Although the person_id is repeated in multiple rows, there’s no conflict because the composite primary key (team_id and person_id in combination) is unique for each row.

How to Create a Composite Primary Key in MySQL

The following is an example of how to create a table with a composite primary key in MySQL:

CREATE TABLE team_members (
    team_id INT,
    person_id INT,
    PRIMARY KEY (team_id, person_id)
);

In this team_members table, the primary key comprises both team_id and person_id. This implies that the pair of team_id and person_id has to be unique for all rows in the table, ensuring that an individual can only be a member of a specific team once.