#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!

Dudeen

Answer 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.

Antitank

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! – Dudeen

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. – Centrosphere

Answer 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.

Custos

Answer 3

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

Consols

Answer 4

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
);

Poaceous

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. – Poaceous

Answer 5

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.

Fino

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 :) – Samons

Answer 6

I was getting the same error (Duplicate entry '' for key 'unique_id') when trying to add a new column as unique "after" I had already created a table containing just names of museums. I wanted to go back and add a unique code for each museum name, with the intention of inserting the code values one at a time. Poor table planning on my part. My solution was to add the new column without making it unique; then entered the data for each code one row at a time; and then changing the column structure to make it unique for future entries. Lucky there were only 10 rows.

Escarp