Error Duplicate entry "1-11" for key "user_image" while creating a 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 I'm going to add is:

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

Result:

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

How to add a UNIQUE index?

Answer 1

Your newly added UNIQUE does not work because your table already has duplicate rows that violate it. Find the violators of the restrictions with the request, as shown below. You cannot add a UNIQUE index while 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