Remove Uniqueness Constraint from MySQL Table

You can use DROP INDEX for this. The syntax is as follows −

alter table yourTablename drop index yourUniqueName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table removeUniquenessConstraint
   -> (
   -> Id int,
   -> Name varchar(100),
   -> Age int,
   -> isGreaterThan18 bool,
   -> UNIQUE(Id,isGreaterThan18)
   -> );
Query OK, 0 rows affected (0.69 sec)

Now check the details of table with the help of SHOW CREATE command. The query is as follows −

mysql> show create table removeUniquenessConstraint;

Here is the output −

+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Create Table                                                     |
+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| removeUniquenessConstraint | CREATE TABLE `removeuniquenessconstraint` (`Id` int(11) DEFAULT NULL,`Name` varchar(100) DEFAULT NULL,`Age` int(11) DEFAULT NULL,`isGreaterThan18` tinyint(1) DEFAULT NULL,UNIQUE KEY `Id` (`Id`,`isGreaterThan18`)) ENGINE =InnoDB DEFAULT CHARSET =utf8             |
+----------------------------+---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The following is the query to remove a uniqueness constraint from a MySQL table −

mysql> alter table removeUniquenessConstraint drop index `Id`;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check all the details of table once again. The query is as follows −

mysql> show create table removeUniquenessConstraint;

Here is the output −

+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table       |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| removeUniquenessConstraint | CREATE TABLE `removeuniquenessconstraint` (`Id` int(11) DEFAULT NULL,`Name` varchar(100) DEFAULT NULL,`Age` int(11) DEFAULT NULL,`isGreaterThan18` tinyint(1) DEFAULT  NULL) ENGINE =InnoDB DEFAULT CHARSET =utf8         |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Look at the above sample output, there is no uniqueness constraint.