Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry ” for key ‘anykey’

MySQLmysql-5.5unique-constraint

I dropped my UNIQUE KEY index 'myindex' on a table
and I wanted to change the UNIQUE KEY to something else,
so after dropping it I did:

alter table mytable add unique key anykey (add1, city, state);

I now get the following message on a key that does not exist
(it doesnt seem to matter what key I change 'anykey' to, I always
get the same error:

ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dumped the entire DB and reloaded it, but still cannot seem to set
a unique key on 'mytable'.

Anyone know what I should check or how to get around this issue?
I WANT a unique key on this table.

The tables important parts:

PRIMARY KEY (`id`)
ENGINE=MyISAM AUTO_INCREMENT=3433 DEFAULT CHARSET=latin1

Thank you in advance for any help!

EDIT: here's show create table

| mytable | CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`name_aka` varchar(100) DEFAULT NULL,
`in_add1` varchar(50) NOT NULL,
`in_add2` varchar(50) NOT NULL,
`in_city` varchar(50) NOT NULL,
`in_state` varchar(2) NOT NULL,
`in_zip` varchar(5) NOT NULL,
`in_zip4` varchar(4) NOT NULL,
`ship_add1` varchar(50) NOT NULL,
`ship_add2` varchar(50) NOT NULL,
`ship_city` varchar(50) NOT NULL,
`ship_state` varchar(2) NOT NULL,
`ship_zip` varchar(5) NOT NULL,
`ship_zip4` varchar(4) NOT NULL,
`phone` varchar(14) NOT NULL,
`phoneExt` varchar(6) NOT NULL,
`phone2` varchar(14) NOT NULL,
`phone2Ext` varchar(6) NOT NULL,
`fac_fax` varchar(6) NOT NULL,
`gender` varchar(7) NOT NULL,
`newsletter_ok` enum('Y','N') DEFAULT 'Y',
`notes` varchar(300) NOT NULL,
`fac_type` varchar(14) NOT NULL,
`fac_contact_email` varchar(50) NOT NULL,
`ind_eng_bks_shipped` varchar(6) NOT NULL,
`ind_span_bks_shipped` varchar(6) NOT NULL,
`ind_eng_s_bks_shipped` varchar(6) NOT NULL,
`ind_span_s_bks_shipped` varchar(6) NOT NULL,
`indiv_other3_bks_shipped` varchar(6) DEFAULT '0',
`cases_eng_bks_shipped` varchar(6) DEFAULT '0',
`cases_span_bks_shipped` varchar(6) DEFAULT '0',
`cases_other1_bks_shipped` varchar(6) DEFAULT '0',
`cases_other2_bks_shipped` varchar(6) DEFAULT '0',
`cases_other3_bks_shipped` varchar(6) DEFAULT '0',
`last_update_by` varchar(20) NOT NULL,
`last_update_date` datetime NOT NULL,
`rev` varchar(9) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3435 DEFAULT CHARSET=latin1 |

I removed the —- stuff obviously.

Best Answer

If you want to hunt down what key combinations are preventing the unique key from working, run this

SELECT add1, city, state,COUNT(1) rowcount
FROM mytable GROUP BY add1, city, state
HAVING COUNT(1) > 1;

To see the actual rows, run this

SELECT A.* FROM mytable A INNER JOIN
(SELECT add1, city, state,COUNT(1) rowcount
FROM mytable GROUP BY add1, city, state
HAVING COUNT(1) > 1) B USING (add1, city, state);

This will give you the opportunity to clean up the data

Alternatively, you may decide to make it a regular nonunique index.