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.
Published on 01-Apr-2019 11:44:05
