How to resolve MySQL error 1962 duplicate entry

The MySQL error 1962: Duplicate entry happens when you try to insert a value that already exists into a table column with a UNIQUE constraint.

A table column with a UNIQUE constraint must have a unique value for all its rows.

The UNIQUE constraint is automatically applied to the table column(s) marked as the PRIMARY KEY

For example, suppose you have a Friends table with the following structure:

CREATE TABLE `Friends` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

Since the id column is the PRIMARY KEY of the Friends table, you can’t insert an identical value to the id column twice.

You can try this by executing the same INSERT statement twice:

mysql> INSERT INTO Friends VALUES ( 1, "Jack", "[email protected]" );

-- Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO Friends VALUES ( 1, "Jack", "[email protected]" );

-- ERROR 1062 (23000): Duplicate entry '1' for key 'friends.PRIMARY'

Since the Friends table already has a row with the id value of 1, the second INSERT statement must have a different value for the id column:

mysql> INSERT INTO Friends VALUES ( 2, "Jack", "[email protected]" );

-- Query OK, 1 row affected (0.01 sec)

Next, you can add a UNIQUE constraint to any table column that you want to have unique-only values.

For example, let’s add the UNIQUE constraint to the email column with the following ALTER TABLE statement:

ALTER TABLE Friends
  ADD UNIQUE (email);

Now the email column is also UNIQUE like the id column.

You can’t insert a value that already exists in the email column:

mysql> INSERT INTO Friends VALUES ( 3, "Jack", "[email protected]" );

-- ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'friends.email'

Finally, if you want to remove the UNIQUE constraint and allow duplicate values in your table column(s), you need to remove the constraint added to the table.

To drop UNIQUE constraints, use the ALTER TABLE statement with the DROP INDEX clause as follows:

ALTER TABLE Friends
  DROP INDEX email;

-- remove UNIQUE from email

To drop the PRIMARY KEY constraint, use the ALTER TABLE statement with the DROP PRIMARY KEY clause like this:

ALTER TABLE Friends
  DROP PRIMARY KEY;

-- remove PRIMARY KEY from Friends table

If your table’s primary key is an AUTO_INCREMENT column, you’ll receive an error 1075 like this:

ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

This is because an AUTO_INCREMENT column must be defined as a key. You need to remove both the PRIMARY KEY constraint and the AUTO_INCREMENT modifier from the column.

To do so, update your ALTER TABLE statement and add a MODIFY clause after the DROP PRIMARY KEY clause as shown below:

ALTER TABLE Friends
  DROP PRIMARY KEY,
  MODIFY id int NOT NULL;

You may need to edit the MODIFY clause to match your table’s definition.

Now you should be able to INSERT duplicate values into your column(s).