MySQL allows us to easily add a primary key constraint to an existing column. We can use the ALTER TABLE statement to achieve this outcome.

We can also use the ADD PRIMARY KEY clause, which can be useful for adding composite primary keys.

Example

Suppose we create a table without a primary key like this:

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

And let’s say we identify the AccountNumber column as the most suitable column for being the primary key.

In that case, we can run the following statement to make it the primary key:

ALTER TABLE Accounts 
MODIFY COLUMN AccountNumber INT NOT NULL PRIMARY KEY;

Result:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

In this case, zero rows were affected. That’s because our table doesn’t yet contain any data. Regardless, the primary key was added as defined.

There are many ways to check the primary key. One way is with the SHOW CREATE TABLE statement:

SHOW CREATE TABLE Accounts;

Result:

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

We can see that the PRIMARY KEY() clause appears at the end of the CREATE TABLE statement. We could use this SQL statement to create the table again if needed. And when we do so, the primary key would already be created (so there would be no need to add the primary key afterwards)

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

Add a Composite Primary Key

A composite primary key is one that’s applied to more than one column. If we want to add a composite primary key to the table, we can do it with the ADD PRIMARY KEY clause of the ALTER TABLE statement.

First, let’s remove the previous primary key:

ALTER TABLE Accounts DROP PRIMARY KEY;

Now let’s add a composite primary key:

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 use SHOW CREATE TABLE to check the primary key:

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 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

So this statement will recreate the table along with the composite 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.