How to Modify Table to Use Composite Primary Key in MySQL

I recently needed to perform a database migration that involved altering a table to use a composite primary key instead of an ordinary primary key.

A composite primary key is a single primary key composed of multiple fields, meaning the combination of all the specified fields must be unique.

Suppose we have a table DogTable with a primary key id, and we’d like to create a new primary key on the fields dog and age.

Adding a Composite Primary Key

If we just want to add a composite primary key, we can do so using ALTER TABLE.

ALTER TABLE DogTable ADD PRIMARY KEY(dog, age);

Let’s say we want to drop the old primary key and add the new composite primary key in one statement.

ALTER TABLE DogTable DROP PRIMARY KEY, ADD PRIMARY KEY(dog, age);