How do I remove a uniqueness constraint from a 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.