How can we remove unique constraint from MySQL table?

To remove a UNIQUE constraint from a MySQL table, first, we must check the name of the index created by the UNIQUE constraint on the table. We know that SHOW INDEX statement is used for this purpose. 'key_name' in the result set of the SHOW INDEX statement contains the name of the index. Now, you can use DROP INDEX statement or ALTER TABLE statement to drop UNIQUE constraints. The syntax of the two 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 a table named 'empl' which has a The only constraint. The index name can be checked by −

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 if we want to remove 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 following query will show that there is no unique on column 'empno' Constraints −

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 run show index from empl query, MySQL returns an empty set as shown below −

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

Using the DROP INDEX statement we can get the result from Delete the UNIQUE constraint in the 'empl' table, as shown below:

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

The above is the detailed content of How can we remove unique constraint from MySQL table?. For more information, please follow other related articles on the PHP Chinese website!