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
Adding a Composite Primary Key
If we just want to add a composite primary key, we can do so using
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);