How to drop unique in MySQL?

Question

Create Table: CREATE TABLE `fuinfo` (
  `fid` int(10) unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `email` varchar(128) NOT NULL,
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

I want to drop the unique key on email,how?

Answer 1

For MySQL 5.7.11

Step-1: First get the Unique Key

Use this query to get it:

1.1) SHOW CREATE TABLE User;

In the last, it will be like this:

.....

.....

UNIQUE KEY UK_8bv559q1gobqoulqpitq0gvr6 (phoneNum)

.....

....

Step-2: Remove the Unique key by this query.

ALTER TABLE User DROP INDEX UK_8bv559q1gobqoulqpitq0gvr6;

Step-3: Check the table info, by this query:

DESC User;

This should show that the index is removed

Thats All.

Answer 2

mysql> DROP INDEX email ON fuinfo;

where email is the unique key (rather than the column name). You find the name of the unique key by

mysql> SHOW CREATE TABLE fuinfo;

here you see the name of the unique key, which could be email_2, for example. So...

mysql> DROP INDEX email_2 ON fuinfo;

mysql> DESCRIBE fuinfo;

This should show that the index is removed

Answer 3

ALTER TABLE 0_value_addition_setup  DROP  INDEX   value_code

Answer 4

This may help others

alter table fuinfo drop index fuinfo_email_unique

Answer 5

 ALTER TABLE [table name] DROP KEY [key name];

this will work.

Answer 6

There is a better way which don't need you to alter the table:

mysql> DROP INDEX email ON fuinfo;

where email is the name of unique key (index).

You can also bring it back like that:

mysql> CREATE UNIQUE INDEX email ON fuinfo(email);

where email after IDEX is the name of the index and it's not optional. You can use KEY instead of INDEX.

Also it's possible to create (remove) multicolumn unique indecies like that:

mysql> CREATE UNIQUE INDEX email_fid ON fuinfo(email, fid);
mysql> DROP INDEX email_fid ON fuinfo;

If you didn't specify the name of multicolumn index you can remove it like that:

mysql> DROP INDEX email ON fuinfo;

where email is the column name.