Error Duplicate entry '1-11' for key 'user_image' when creating new UNIQUE index

Question

My table structure:

CREATE TABLE `userimageview` (
  `user_id` int(11) unsigned NOT NULL,
  `image_id` int(11) unsigned NOT NULL,
  `thumbnail_view` int(10) unsigned NOT NULL,
  `fullsize_view` int(10) unsigned NOT NULL,
  `point` int(10) unsigned NOT NULL,
  KEY `everything` (`user_id`,`image_id`,`thumbnail_view`,`fullsize_view`,`point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The index that I'm going to add:

ALTER TABLE `userimageview` ADD UNIQUE `user_image` (`user_id` , `image_id`)

Result:

#1062 - Duplicate entry '1-11' for key 'user_image' 

How should I add my UNIQUE index?

Methodize

Sounds like you have a row that violates your constraint. SELECT * FROM userimageview WHERE user_id = 1 AND image_id = 11 – Brubaker

Better yet, find all the duplicates: SELECT user_id, image_id, COUNT(*) AS dupes FROM userimageview GROUP BY user_id, image_id HAVING dupes > 1 ORDER BY dupes DESC – Brubaker

Oh, yes. You are right @Michael. I thought that there should be a STRUCTURAL error! Please post your solution as an answer so that I can accept it :) – Methodize

We should wait 5 more minutes. Thank you anyway. I was struggling with it all night – Methodize

Answer 1

Your newly added UNIQUE constraint is failing because your table already contains duplicate rows that violate it. Locate the constraint violators with a query like the following. You won't be able to add the UNIQUE index as long as these rows are present.

SELECT
  user_id, 
  image_id, 
  COUNT(*) AS dupes
FROM userimageview
GROUP BY user_id, image_id
HAVING dupes > 1
ORDER BY dupes DESC

Brubaker