Dropping Unique constraint from MySQL table?

First, let us create a table with the UNIQUE constraint. It suggests that we cannot add duplicate values.

Creating a table.

mysql> create table UniqueConstraintDemo
   -> (
   -> Name varchar(200) unique
   -> );
Query OK, 0 rows affected (1.05 sec)

Now, we can check whether the table has UNIQUE constraint or not with the help of DESC command. The query is as follows.

mysql> DESC UniqueConstraintDemo;

The following is the output.

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Name  | varchar(200) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Now, we have unique constraint on column ‘Name’. Therefore, we cannot add duplicate values. If we try to insert same value twice then we will get an error. The query to insert records are as follows.

mysql> insert into UniqueConstraintDemo values('John');
Query OK, 1 row affected (0.54 sec)

mysql> insert into UniqueConstraintDemo values('John');
ERROR 1062 (23000): Duplicate entry 'John' for key 'Name'

Look at the Error 1062 above. It suggests that we tried adding duplicate values to a column with UNIQUE constraint.

Let us now see the syntax for dropping the UNIQUE constraint.

DROP index yourColumnName on yourTableName;

Apply the above syntax to remove the unique constraint.

mysql> DROP index Name on UniqueConstraintDemo;
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

We can now check whether UNIQUE constraint has been removed or not with the help of DESC command. The query is as follows −

mysql> DESC UniqueConstraintDemo;

The following is the output that displaying we have successfully removed UNIQUE constraint.

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Name  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)