How to Modify Table to Use Composite Primary Key in MySQL

Published Oct 14, 2021

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);

Dropping and Adding a Composite Primary Key

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);

More SQL Articles