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

George John

Published on 16-Feb-2018 13:20:24

• Related Questions & Answers
• Dropping Unique constraint from MySQL table?
• How can we check the indexes created by a UNIQUE constraint on a MySQL table?
• How can we apply UNIQUE constraint to the field of an existing MySQL table?
• What is MySQL UNIQUE constraint and how can we apply it to the field of a table?
• How can we drop a MySQL view from the database?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
• How can we remove NOT NULL constraint from a column of an existing MySQL table?
• Adding unique constraint to ALTER TABLE in MySQL
• How can I drop an existing column from a MySQL table?
• How can we drop a MySQL stored procedure?
• How can we remove a column from MySQL table?
• How can we apply a NOT NULL constraint to a column of an existing MySQL table?
• How can we delete multiple rows from a MySQL table?
• How can we delete all rows from a MySQL table?