How to remove compound UNIQUE KEY in MySQL?
This question has been asked before, but they all refer to single keys, not compound keys, and the solutions don't seem to work for me. Basically, please consider the following table:
CREATE TABLE IF NOT EXISTS `my_answers` (
`id` int(11) NOT NULL auto_increment,
`question_id` int(11) NOT NULL default 0,
`user_id` int(11) NOT NULL default 0,
PRIMARY KEY (`id`),
UNIQUE KEY (`question_id`, `user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
How can I remove the unique key from question_id and user_id in this case? I've tried the following:
ALTER TABLE my_answers DROP INDEX `UNIQUE`;
and DROP INDEX UNIQUE
ON my_answers;
Both of them fail causing the following error "# 1091 - cannot DROP" UNIQUE ", check if column / key exists"
Any help is greatly appreciated, thanks!
source to share
If you do not provide a unique key name in the case of a composite key, the default is the name of the first column.
Here's an example
mysql> CREATE TABLE IF NOT EXISTS `my_answers` (
-> `id` int(11) NOT NULL auto_increment,
-> `question_id` int(11) NOT NULL default 0,
-> `user_id` int(11) NOT NULL default 0,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY (`question_id`, `user_id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
If you run now show create table
, you can see something like
mysql> show create table my_answers ;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_answers | CREATE TABLE `my_answers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`question_id` int(11) NOT NULL DEFAULT '0',
`user_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `question_id` (`question_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
Here you can see that the key name is the first column in the composite key that is question_id
, and you can leave it as
mysql> alter table `my_answers` drop INDEX question_id ;
Query OK, 0 rows affected (0.09 sec)
source to share