How can we drop UNIQUE constraint from a MySQL table?

For dropping UNIQUE constraint from a MySQL table, first of all, we must have to check the name of the index created by the UNIQUE constraint on the table. As we know that SHOW INDEX statement is used for this purpose. The ‘key_name’ in the result set of SHOW INDEX statement contains the name of the index. Now either with the help of DROP INDEX statement or ALTER TABLE statement, we can drop the UNIQUE constraint. The syntax for both the statements is as follows −

Syntax

DROP INDEX index_name ON table_name;
OR
ALTER TABLE table_name DROP INDEX index_name;

Example

Suppose we have the table ‘empl’ which have a UNIQUE constraint on column ‘empno’. The index name can be checked as follows −

mysql> Show Index from empl\G
*************************** 1. row ***************************
Table: empl
Non_unique: 0
Key_name: empno
Seq_in_index: 1
Column_name: empno
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.02 sec)

Now for dropping the UNIQUE constraint, we can write the following query −

mysql> ALTER TABLE empl DROP INDEX empno;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

The result set of the query below will show that there is no UNIQUE constraint on column ‘empno’ −

mysql> describe empl;

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| empno | int(11) | YES | | NULL | |
| F_name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

2 rows in set (0.04 sec)

Even if we will run the SHOW INDEX from empl query then MySQL will result in an empty set as follows −

mysql> Show index from empl;
Empty set (0.00 sec)

The UNIQUE constraint can also be dropped from ‘empl’ table with the help of DROP INDEX statement as follows −

mysql> DROP INDEX empno on empl;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0