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?

Sarette

Answer 1

Simply you can use the following SQL Script to delete the index in MySQL:

alter table fuinfo drop index email;

Stephenstephenie

Note that you may not be able to drop a key like this if a foreign key to the email column exists (error 150) . To get this to work, drop the foreign key first, then drop the index, and recreate the foreign key afterwards. e.g ALTER TABLE fuinfo DROP foreign key fk_name_for_email; – Morbilli

Answer 2

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.

Grappling

all of these will actually do an ALTER TABLE anyways, so it's not like you're really avoiding that. It's just a different syntax for the same thing. – Barbaresi

Answer 3

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

Kristianson

Answer 4

Use below query :

ALTER TABLE `table_name` DROP INDEX key_name;

If you don't know the key_name then first try below query, you can get key_name.

SHOW CREATE TABLE table_name

OR

SHOW INDEX FROM table_name;

If you want to remove/drop primary key from mysql table, Use below query for that

ALTER TABLE `products` DROP INDEX `PRIMARY`;

Code Taken from: http://chandreshrana.blogspot.in/2015/10/how-to-remove-unique-key-from-mysql.html

Prolongate

Answer 5

DROP INDEX column_name ON table_name

Select the database and query form the sql tab.This removes the index of the particular column. It worked for me in PHP MyADMIN

Dartmoor

Answer 6

This may help others

alter table fuinfo drop index fuinfo_email_unique

Alkoran

Answer 7

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.

Libido

Answer 8

ALTER TABLE 0_value_addition_setup  DROP  INDEX   value_code

Novgorod

Answer 9

Try it to remove uique of a column:

ALTER TABLE  `0_ms_labdip_details` DROP INDEX column_tcx

Run this code in phpmyadmin and remove unique of column

Checker

Answer 10

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

this will work.

Nicknack