CODEWITHSUNDEEP
mysqlsql
Mike Feng
Mike Feng

Reputation: 833

How to drop composite UNIQUE KEY in MySQL?

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!

Upvotes: 7

Views: 7641

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

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)

Upvotes: 7

Mike Feng
Mike Feng

Reputation: 833

I have found the answer, turns out it was ridiculously simple. Simply remove one of the field as index key:

ALTER TABLE my_answers DROP INDEX user_id

Turns out this will dismantle the composite key.

Upvotes: 0

ro0ter
ro0ter

Reputation: 439

Try the following command:

show index from `my_answers`;

then inspect the key name of your index and drop it by its name.

Upvotes: 2

Related Questions