#1062 - Duplicate entry '' for key 'unique_id' When Trying to add UNIQUE KEY (MySQL)

Question

I've got an error on MySQL while trying to add a UNIQUE KEY. Here's what I'm trying to do. I've got a column called 'unique_id' which is VARCHAR(100). There are no indexes defined on the table. I'm getting this error:

#1062 - Duplicate entry '' for key 'unique_id' 

When I try to add a UNIQUE key. Here is a screenshot of how I'm setting it up in phpMyAdmin:

Here is the MySQL query that's generate by phpMyAdmin:

ALTER TABLE  `wind_archive` ADD  `unique_id` VARCHAR( 100 ) NOT NULL FIRST ,
ADD UNIQUE (
`unique_id`
)

I've had this problem in the past and never resolved it so I just rebuilt the table from scratch. Unfortunately in this case I cannot do that as there are many entries in the table already. Thanks for your help!

Solution 1

The error says it all:

Duplicate entry '' 

So run the following query:

SELECT unique_id,COUNT(unique_id)
FROM yourtblname
GROUP BY unique_id
HAVING COUNT(unique_id) >1

This query will also show you the problem

SELECT *
FROM yourtblname
WHERE unique_id=''

This will show you where there are values that have duplicates. You are trying to create a unique index on a field with duplicates. You will need to resolve the duplicate data first then add the index.

Solution 2

This is 3rd time i am looking for solution to this problem so for the reference I am posting the answer here.

Depending on the data we may use IGNORE keyword with Alter command. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

The IGNORE keyword extension to MySQL seems to have a bug in the InnoDB version on some version of MySQL.

You could always, convert to MyISAM, IGNORE-ADD the index and then convert back to InnoDB

ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;

Note, if you have Foreign Key constraints this will not work, you will have to remove those first, and add them back later.

Solution 3

Make unique_id NULL from NOT NULL and it will solve your problem

Solution 4

because of you write in your query, unique_id be NOT NULL and previous rows all of them are null and you want this column be unique, then after run this query, you have several rows with the same value it means this column is not unique, then you have to change unique_id NOT NULL to unique_id NULL in your query.

Solution 5

select ID from wind_archive 
where ID not in (select max(ID) from wind_archive group by unique_id) 

and this is what you should remove from the table before you succesfully add the unique key. this also works for adding unique key with 2 or more columns. such as -

delete from wind_archive 
where ID in (
select * from (select ID from wind_archive where ID not in (
select max(ID) from wind_archive group by lastName, firstName
) ORDER BY ID
) AS p
);

View more solutions

Comments

[MillerMedia]

Oh ok, I get it now. Yeah that's actually the reason I'm creating the 'unique' value on the index. I didn't know exactly what the error meant (I was overthinking it). Thanks so much, that gets me pointed in the right direction!

[Varun Nath]

Since your adding a unique column to a table already populated with data, it will throw this error as it will try and duplicate the value ' ' in each rows. If you try and add this column to an empty table, you will not have any problem.

[Ning Yu Fisher]

if there are some rows with their IDs doesn't equal to the MAX(ID) of GROUP BY unique_id - they are all duplications; on the contrary, suppose there is no duplication, all the ID with GROUP BY unique_id should equal to its own MAX(ID), because it's itself.

[Jodyshop]

Correct. That because other field values were empty, so, When I make it NULL it can be added successfully without showing the error message. Thanks for the hint :)