If you’re getting an error that reads something like “Multiple primary key defined” in MySQL, it’s probably because you’re defining more than one primary key to a table.

A table can only have one primary key in MySQL.

It’s possible that you were trying to create a composite primary key (i.e. a primary key consisting of multiple columns), in which case, you can fix the issue with a simple modification to your code.

Otherwise, you will need to decide which column should be the primary key for the table and only apply the PRIMARY KEY definition to that column.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE Person
(
    PersonId INT NOT NULL PRIMARY KEY,
    VoucherCode INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

ERROR 1068 (42000): Multiple primary key defined

MySQL correctly tells us that that there’s an error because I tried to create two primary keys for the table.

Solution 1

The solution will depend on whether or not you want to create a composite primary key (one that consists of multiple columns).

If you want to create a composite primary key, you can define it at the end of the CREATE TABLE statement (i.e. after all the columns have been defined).

Example:

CREATE TABLE Person
(
    PersonId INT NOT NULL,
    VoucherCode INT NOT NULL,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    PRIMARY KEY (PersonId, VoucherCode)
);

Result:

Query OK, 0 rows affected (0.01 sec)

When we do this we include each column in parentheses, separated by a column.

This time the code ran without error.

Solution 2

If you don’t want to create a composite primary key, then you’ll need to decide which column should be the primary key and only apply the PRIMARY KEY option to that column.

We can do this using either syntax from above (either in the column definition or after all column definitions).

Here’s an example of including it in the column definition:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    PersonId INT NOT NULL PRIMARY KEY,
    VoucherCode INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.00 sec)

Again, the table (and its primary key) were successfully created.