2

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.

migrated from serverfault.com Oct 6 '14 at 20:23

This question came from our site for system and network administrators.

  • Could you also include show create table output? – Glueon Oct 6 '14 at 18:34
  • I should add I changed the in_add1, in_city, and in_state in my alter statement when I originally posted, but setting it to the proper values still gives me the same results (I assumed anyone reading this knows that). Thanks – Tim Oct 6 '14 at 19:49
  • 1
    You have 2 or more rows with same values in the 3 columns (probably empty strings on all 3). – ypercubeᵀᴹ Oct 6 '14 at 20:51
  • When I was manually correcting the DB, I kept running the alter statement above and it kept telling me the rows to fix. When it was finally done complaining, I got that ERROR message. I wrote a perl script and hashed the lower case of all 3 columns and find no duplicates there either. – Tim Oct 6 '14 at 21:12
  • My bad, I read the output wrong, I have a bunch, ugh. Thanks – Tim Oct 6 '14 at 21:14
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.