How to Remove Unique Constraint in MySQL

In today’s post, we’ll look at the methods for dropping or removing a unique constraint in MySQL.

Remove Unique Constraints in MySQL

You may occasionally wish to guarantee that each value in a column or a collection of columns is distinct. For instance, user email addresses in the employees’ database or customer phone numbers in the customers’ table should differ.

A UNIQUE constraint is used to enforce this rule.

An integrity constraint called UNIQUE assures that each value in a column or a combination of columns is distinct. A table or column constraint can be a UNIQUE constraint.

A related UNIQUE index is created by MySQL when a unique constraint is defined, and this index is then used to enforce the constraint.

A frequently used column can be indexed to speed up SELECT queries by allowing MySQL to avoid searching the full table. At times, dropping indices might be advantageous.

When a record is altered, MySQL must update any indexes that include the modified columns. Your table may be over-indexed if you don’t use a particular index; removing the index would increase the efficiency of table updates.

Syntax:

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX index_name_1, DROP INDEX index_name_2;

Here, table_name denotes the name of the table from which the index should be deleted, and index_name denotes the name of the index you desire to delete.

When you delete an index that is not a PRIMARY KEY, you must include the index_name in your statement. If you don’t know the name, use SHOW INDEX.

The last sentence illustrates how you can do several drop actions with a single ALTER TABLE query, provided you divide the operations with commas.

To further understand the previous concept, consider the following example:

DROP INDEX email ON Employees;
ALTER TABLE Employees DROP INDEX email;

In the above example, we are eliminating the email index, a unique constraint on the Employees table. The email column and associated data won’t be affected; the index will only be destroyed.

Users may add multiple cases of the same email value without encountering problems.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

successfully dropped index