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);
More SQL Articles
- How to Format a Number with Two Decimal Places in SQLite
- How to Order By Multiple Columns in SQL
- How to Count the Number of Rows with the Same Value
- How to Order a SQL String Column By Its Numeric Value
- How to Check if a Column is Null in MySQL
- How to Combine Columns Values Into a New Column in MySQL
- How to Drop Multiple Columns with ALTER TABLE in SQL
- What Stripe Data Do I Store in My Database?
- How To Query JSON Object with Unknown Keys in PostgreSQL
- How To Convert an Escaped JSON String in PostgreSQL to a JSON Object