MySQL DROP UNIQUE CONSTRAINT

Unique Constraint allows you to enforce uniqueness of records in your database table and maintain database integrity. Here’s how to drop unique constraint from MySQL table using MySQL DROP INDEX command. It is also used to remove unique key in MySQL.

Table of Contents

What is Unique Constraint in MySQL?

Unique constraint for a table is a combination of one or more fields that uniquely define each record in the table. These fields can contain null values as long as the combination of field values is unique.

Bonus Read : DROP FOREIGN KY CONSTRAINT

How to Drop Unique Constraint in MySQL

Here are the steps to drop unique constraint in MySQL. We will use MySQL DROP INDEX query to remove existing index from a table.

Here’s the syntax of MySQL DROP INDEX command

DROP INDEX constraint_name ON table_name
[algorithm_option | lock_option];

In the above query, constraint_name is the name of constraint, and table_name is your database table name.

Please note, whether you want to DROP INDEX or DROP UNIQUE CONSTRAINT, you need to use DROP INDEX query.

Bonus Read : MySQL DROP DATABASE

You can also provide 2 optional arguments – algorithm_option and lock_option.

algorithm_option can have either of 3 values – default, inplace, copy. Depending on your argument, MySQL will use different algorithms to drop unique constraint.

Its syntax is

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

default – This has the same effect as not using algorithm_option argument
copy – The table where you need to delete constraint, will be copied and the index will be deleted from the copy table. During this time, concurrent operations such as INSERT and UPDATE are not permitted.
inplace – In this case, the table is rebuilt in place without constraint. Although this option allows concurrent operations, it puts a metadata lock during execution.

Depending on lock_option MySQL will lock/not lock read/write access to your tables while constraint is being deleted. lock_option has the following syntax

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

It takes 4 values:

default – it allows maximum concurrency for your choice of algorithm. it allows both concurrent reads and writes if supported. If not, it allows concurrent reads only. If that is also not supported, it enforces exclusive access.
none – if supported, then you can have concurrent reads and writes. Otherwise, MySQL gives an error.
shared – if shared option is supported, you can have concurrent reads, but not concurrent writes.
exclusive – this option enforces exclusive access

Bonus Read : MySQL DROP COLUMN

MySQL DROP UNIQUE CONSTRAINT example

Let’s say you have the following table with unique constraint.

mysql> create table orders(order_id int primary key,
     order_date date,
     product varchar(255),
     sale int,
     constraint name unique (product)
     );

Here’s the SQL query to drop unique constraint name from table orders.