sql DROP CONSTRAINT UNIQUE not working

Question

I got the following table:

CREATE TABLE `unsub_counts` (
 `count_id` int(11) NOT NULL AUTO_INCREMENT,
 `unsub_date` date DEFAULT NULL,
 `unsub_count` int(11) DEFAULT NULL,
 `store_id` smallint(5) DEFAULT NULL,
 PRIMARY KEY (`count_id`),
 UNIQUE KEY `uc_unsub_date` (`unsub_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

where column unsub_date is unique. Now I want to drop that uniqueness, because I need to have a unique index on unsub_date + store_id.

I found suggestions on the net, but is failing:

ALTER TABLE `unsub_counts` DROP CONSTRAINT `unsub_date`

gives me: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT unsub_date' at line 1

Is this related to MyISAM? Any other suggestions?

I don't use MySQL but it looks like you're not passing in a constraint name for one, rather a column name. — ta.speot.is, Dec 01 '12 at 11:48
FWIW I Googled and found http://codeghar.wordpress.com/2008/03/28/drop-unique-constraint-in-mysql/ which suggests dropping the concomitant index — ta.speot.is, Dec 01 '12 at 11:50

score 1 · Accepted Answer · answered Dec 01 '12 at 11:50

1

Use drop index with constraint name:

ALTER TABLE unsub_counts DROP INDEX uc_unsub_date;

or just:

drop index uc_unsub_date on unsub_counts;

answered Dec 01 '12 at 11:50

Michał Powaga

22,561
8
51
62

I missed the name of the index starting with uc_. Thx, it worked – axel wolf Dec 01 '12 at 12:45

sql DROP CONSTRAINT UNIQUE not working

1 Answers1

Linked