How to remove unique key from mysql table

Question

I need to remove a unique key from my mysql table. How can remove that using mysql query.

I tried this but it is not working

alter table tbl_quiz_attempt_master drop unique key;

Please help me

Thanks

Shemeka

duplicate post to https://mcmap.net/q/158202/-how-to-drop-unique-in-mysql – Belindabelisarius

Possible duplicate of How to drop unique in MySQL? – Notus

Answer 1

All keys are named, you should use something like this -

ALTER TABLE tbl_quiz_attempt_master
  DROP INDEX index_name;

To drop primary key use this one -

ALTER TABLE tbl_quiz_attempt_master
  DROP INDEX `PRIMARY`;

ALTER TABLE Syntax.

Gynecocracy

Answer 2

First you need to know the exact name of the INDEX (Unique key in this case) to delete or update it.
INDEX names are usually same as column names. In case of more than one INDEX applied on a column, MySQL automatically suffixes numbering to the column names to create unique INDEX names.

For example if 2 indexes are applied on a column named customer_id

1. The first index will be named as customer_id itself.
2. The second index will be names as customer_id_2 and so on.

To know the name of the index you want to delete or update

SHOW INDEX FROM <table_name>

as suggested by @Amr

To delete an index

ALTER TABLE <table_name> DROP INDEX <index_name>;

Gains

Answer 3

Here is how to get index_name which is mentioned in Devart's answer or key_name which is mentioned in Uday Sawant's answer:

SHOW INDEX FROM table_name;

This will show all the indexes for the given table_name. And you can pick the name of the index or the unique key you want to remove.

Ridden

I still get: "You have an error in your SQL syntax;" – Whyte

Answer 4

ALTER TABLE mytable DROP INDEX key_Name;

Setup

Answer 5

There are two method two remove index in mysql. First method is GUI. In this method you have to open GUI interface of MYSQL and then go to that database and then go to that particular table in which you want to remove index.

After that click on the structure option, Then you can see table structure and below you can see table indexes. You can remove indexes by clicking on drop option

Second method by

ALTER TABLE student_login_credentials DROP INDEX created_at;

here student_login_credentials is table name and created_at is column name

Apoenzyme

Answer 6

Unique key is actually an index. http://codeghar.wordpress.com/2008/03/28/drop-unique-constraint-in-mysql/

Florenceflorencia

Answer 7

To remove a unique key from a column, you have to run the below query:

ALTER TABLE your_table_name 
    DROP INDEX tableName_columnName_keyName;

Where tableName should be your name of the table followed by an underscore then columnName should be the name of the column which you want to remove from the unique key constraint followed by an underscore and at last keyName should be the name of the key i.e unique in your case.

Skinned

Answer 8

To add a unique key use :

alter table your_table add UNIQUE(target_column_name);

To remove a unique key use:

alter table your_table drop INDEX target_column_name;

Bessel