Create a Composite Primary Key in MySQL

A composite primary key is a primary key that consists of two or more columns. Together they will always provide a unique value within the table. In other words, the combination of both values will be unique across all rows – no two rows will share the same combined value.

In MySQL we can create a composite primary key with the PRIMARY KEY clause of the CREATE TABLE statement. We can also use the ADD PRIMARY KEY statement to add a composite primary key to an existing table that doesn’t already have a primary key.

Create a Table with a Composite Primary Key

Most of the time we create the primary key at the time we create the table. Therefore, we include the primary key definition in the CREATE TABLE statement.

Here’s an example:

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

In this example, we applied the primary key constraint to the AccountNumber and AccountType columns. We achieved this result with the PRIMARY KEY clause of the CREATE TABLE statement. This clause is placed at the end of the statement.

There are many ways to check a table for its primary key in MySQL. For our purposes, let’s use SHOW CREATE 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 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the CREATE TABLE statement has a PRIMARY KEY () clause at the end of it. This statement can be used to recreate the table along with the composite primary key.

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.

Add a Composite Primary Key

Adding a primary key to an existing table requires that the table doesn’t already have a primary key.

So let’s 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.

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.