Remove unique key from MySQL table?

To remove the unique key from MySQL, use the DROP command. The syntax is as follows −

ALTER TABLE yourTableName DROP INDEX yourKeyName;

To understand the above syntax, let us create a table with the unique key. The query to create a table is as follows −

mysql> create table DropIndexDemo
   −> (
   −> BookId int unique key,
   −> BookName varchar(200)
   −> );
Query OK, 0 rows affected (0.88 sec)

Now you can check what is the key name with the help of show command. This unique key will get deleted. The query is as follows −

mysql> show index from DropIndexDemo;

The following is the output −

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| dropindex | 0          | BookId  | 1             | BookId      | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               | YES     |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.17 sec)

Look at the above sample output, your key name is ‘BookId’. Now here is the query to remove unique key −

mysql> alter table DropIndex drop index BookId ;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0

We have removed the unique key from MySQL table DropIndex. The BookId column had unique key before.

Now display the table structure with the help of desc command. This won’t display Unique Key since we have deleted it −

mysql> desc DropIndex;

The following is the output −

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| BookId   | int(11)      | YES  |     | NULL    |       |
| BookName | varchar(200) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Look at the above sample output there is no unique key in column BookId.