How to drop composite UNIQUE KEY in MySQL?

Question

This question has been asked before, but they all referenced to single and not composite 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 do I remove the unique key made of question_id and user_id in this case? I have tried the following:

ALTER TABLE my_answers DROP INDEX `UNIQUE`;

and DROP INDEX UNIQUE ON my_answers;

Both of which didn't work, throwing the following error "#1091 - Can't DROP 'UNIQUE'; check that column/key exists"

Any help is greatly appreciated, thanks!

Answer 1

If you do not specify a name of the unique key in case of composite key then by default the first column name is used as the name.

Here is the 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 now run show create table you can see something as

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 which is question_id and you can drop it as

mysql>  alter table `my_answers` drop INDEX question_id ;
Query OK, 0 rows affected (0.09 sec)