How to remove unique constraint in MySQL?

Question

I was searching online about how to remove unique constraint in MySQL in the Internet and I found a lot of solutions.

1. DROP INDEX index_name ON table_name;
   

2. ALTER TABLE table_name
   DROP INDEX index_name;
   

3. ALTER TABLE table_name
   DROP CONSTRAINT constraint_name; 
   

What's the difference between all these queries? Which one of these queries is according to standard SQL?

Also, what's the difference between unique constraint, unique index, and unique key?

Answer 1

Many users of SQL do not realize that indexes are not in the SQL standard.

Go ahead, try to find CREATE INDEX, DROP INDEX, or any of the other related syntax for indexes in the ANSI/ISO SQL specification.

Constraints are in the specification. Here are links to coverage of UNIQUE CONSTRAINT in the online version of the book "SQL-99, Complete, Really"

• https://crate.io/docs/sql-99/en/latest/chapters/20.html#constraint-type-unique-constraint
• https://crate.io/docs/sql-99/en/latest/chapters/20.html#dropping-constraints

A UNIQUE constraint describes a logical limitation on the behavior INSERT/UPDATE/DELETE operations performed on a given table. That is, no two rows may have the same non-NULL values in the unique column(s) named in the constraint.

But indexes are an implementation detail, and the SQL standard leaves implementation details up to the vendor.

It just happens that adding an index data structure is the most common way to make implementation of a constraint have a hope of being efficient. So virtually all SQL vendors have some kind of feature like that, and they extend SQL syntax with the necessary statements and clauses to support indexes.

It's frankly a miracle that the index syntax is as similar between vendors as it is. This gives users an impression that the vendors are complying with some part of the SQL standard. They are not; they are just mimicking earlier implementations.

---

Re your comment:

These two queries accomplish the same thing. There is no difference in semantics, only syntax.

DROP INDEX index_name ON table_name;

ALTER TABLE table_name
DROP INDEX index_name;

For example, with ALTER TABLE, you could combine dropping the index with other alter operations on the same table, such as dropping multiple indexes, or creating new indexes, or any other alteration.

But DROP INDEX only allows you to drop one index on one table.

Then the third query drops a constraint. Constraints are not indexes.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 

This may have the effect of dropping an index, but not necessarily. It depends on what type of constraint you are dropping. For example, dropping a UNIQUE constraint implicitly drops the index associated with that constraint. Whereas dropping a FOREIGN KEY or CHECK constraint does not drop an index on the same column.

Answer 2

DROP INDEX index_name ON table_name;

and

ALTER TABLE table_name
DROP INDEX index_name;

are absolutely the same. Moreover, first query is really mapped to second one.

---

ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 

performs another function.

Index is a structure within the table. DROP INDEX drops the index (sorry..).

Constraint is a rule within the table, this rule may be accompanied by according index creation for constraint maintainance. DROP CONSTRAINT drops the constraint. But if the index for its maintainance was created then the index is not dropped. See fiddle.

Answer 3

As of MySQL 8.0.19, ALTER TABLE permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint name:

• Drop an existing constraint named symbol:

  ALTER TABLE tbl_name
      DROP CONSTRAINT symbol;
  

Source

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema. Consequently, names for each type of constraint must be unique per schema, but constraints of different types can have the same name. When multiple constraints have the same name, DROP CONSTRAINT and ADD CONSTRAINT are ambiguous and an error occurs. In such cases, constraint-specific syntax must be used to modify the constraint. For example, use DROP PRIMARY KEY or DROP FOREIGN KEY to drop a primary key or foreign key.
Source

So, technically, on MySQL 8.0.19 or above, we can use ALTER TABLE tbl_name DROP CONSTRAINT constraint_name to remove a UNIQUE constraint. But, in case if some other constraint has the same name as UNIQUE constraint, this method won't work. In such cases, we must use ALTER TABLE tbl_name DROP INDEX index_name or DROP INDEX index_name ON tbl_name to remove the UNIQUE constraint.

The difference between ALTER TABLE tbl_name DROP INDEX index_name and DROP INDEX index_name ON tbl_name has been explained in this answer.