If you’re getting an error in MySQL that reads something like “Duplicate entry ‘1’ for key ‘person.PRIMARY’” when trying to add a primary key to an existing table, it’s probably because the table already contains duplicate values in the column you’re trying to add the primary key to.
When we add a primary key to an existing table, we need to make sure that the column/s that we’re adding the primary key to contains unique values across all of its rows.
One way to fix this issue is to create a composite primary key (one that’s defined across multiple columns). Another way to fix it is to change the values so that they’re all unique. Another option is to apply the primary key to a different column altogether (i.e. one that contains unique values).
Example of Error
Suppose we have the following table:
SELECT * FROM Person;Result:
+----------+-------------+-----------+----------+ | PersonId | VoucherCode | FirstName | LastName | +----------+-------------+-----------+----------+ | 1 | 1 | Homer | Simpson | | 1 | 2 | Ned | Flanders | +----------+-------------+-----------+----------+
The PersonId column sounds like it should be the primary key.
Let’s try to add a primary key:
ALTER TABLE Person ADD PRIMARY KEY(PersonId);Result:
ERROR 1062 (23000): Duplicate entry '1' for key 'person.PRIMARY'
We got the error because the PersonId column contains duplicate values. We can see that both rows contain the same value. Primary key columns must contain unique values.
Solution 1
One solution is to see if we could create a composite primary key. A composite primary key is a primary key that consists of multiple columns.
When we look at the above table, we can see that there’s a VoucherCode column. If we know that no two people could have the same PersonId and VoucherCode combined, then we could use those two columns as the primary key.
Here’s how we can create such a primary key:
ALTER TABLE Person ADD PRIMARY KEY(PersonId, VoucherCode);Result:
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
This time the primary key was created successfully.
Solution 2
Another option is to use a different column for the primary key (one that doesn’t contain duplicate values).
First let’s drop the primary key that we created in the previous solution:
ALTER TABLE Person DROP PRIMARY KEY;Result:
Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0
Now let’s set the VoucherCode column as the primary key:
ALTER TABLE Person ADD PRIMARY KEY(VoucherCode);Result:
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Again, it worked without error.
Solution 3
Another option is to change existing values so that there are no duplicates.
Of course, we’d need to be very careful about choosing this option. We’d need to be sure that we’re not messing up anything by changing the data.
First let’s drop the primary key that we created in the previous solution:
ALTER TABLE Person DROP PRIMARY KEY;Result:
Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0
Now let’s change the data in the PersonId column so that there are no duplicate values:
UPDATE Person SET PersonId = 2
WHERE FirstName = 'Ned' AND LastName = 'Flanders';
SELECT * FROM Person;Result:
+----------+-------------+-----------+----------+ | PersonId | VoucherCode | FirstName | LastName | +----------+-------------+-----------+----------+ | 1 | 1 | Homer | Simpson | | 2 | 2 | Ned | Flanders | +----------+-------------+-----------+----------+
Now we can set the PersonId column as the primary key:
ALTER TABLE Person ADD PRIMARY KEY(PersonId);Result:
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0