In MySQL, adding a composite primary key to an existing table is similar to adding a singular primary key. We define the primary key with the ALTER TABLE statement.

However, when adding a composite primary key we need to use the ADD PRIMARY KEY clause. We can’t define it at the column level. With singular primary keys on the other hand, we have the choice of using the ADD PRIMARY KEY clause or applying it at the column level.

Example

Suppose we create a table without a primary key:

DROP TABLE IF EXISTS Accounts;
CREATE TABLE Accounts (
   AccountNumber INT,
   AccountType INT,
   AccountDescription VARCHAR(20)
   );

We can add the composite primary key like this:

ALTER TABLE Accounts
ADD PRIMARY KEY ( AccountNumber, AccountType );

We just set the AccountNumber and AccountType columns as the primary key for this table.

Let’s check the table:

SHOW CREATE TABLE Accounts;

Result:

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                              |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Accounts | CREATE TABLE `Accounts` (
  `AccountNumber` int NOT NULL,
  `AccountType` int NOT NULL,
  `AccountDescription` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`AccountNumber`,`AccountType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

As expected, the composite primary key was added as specified.

We can also see that MySQL has added NOT NULL to our primary key columns. This happens automatically when we create a primary key against a nullable column. Primary keys can’t be nullable.

Trying to Define a Composite Primary Key at the Column Level

As mentioned, we can’t define a composite primary key at the column level like we can do with singular primary keys. Here’s what happens when we try to do that:

DROP TABLE IF EXISTS Accounts;
CREATE TABLE Accounts (
   AccountNumber INT PRIMARY KEY,
   AccountType INT PRIMARY KEY,
   AccountDescription VARCHAR(20)
   );

Result:

ERROR 1068 (42000): Multiple primary key defined

We get an error. MySQL thinks we’re trying to create two primary keys. We can’t do this – a table can only have one primary key.

Existing Data

The above examples were done on a table with no data. If the table contained data, we would need to be sure that there are no duplicate values in the column/s that we’re setting as the primary key. If there are, we’ll get an error.

See 3 Ways to Fix MySQL Error 1062 “Duplicate entry ‘…’ for key” When Adding a Primary Key to an Existing Table to see this error, and proposed options for dealing with it.