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
To see the actual rows, run this
This will give you the opportunity to clean up the data
Alternatively, you may decide to make it a regular nonunique index.