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

indexing, mysql, mysql-error-1062, unique

Solution

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

Problem

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?

Original source