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.

Samual Sam

Published on 01-Apr-2019 11:44:05

• Related Questions & Answers
• How do I add a check constraint to a table in MySQL?
• How do I remove a MySQL database?
• How can we drop UNIQUE constraint from a MySQL table?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
• How can we remove NOT NULL constraint from a column of an existing MySQL table?
• Remove index from a MySQL table
• How do I remove a property from a JavaScript object?
• Dropping Unique constraint from MySQL table?
• How do I alter a MySQL table column defaults?
• How can we remove a column from MySQL table?
• How can I enforce compound uniqueness in MySQL?
• How do I detect if a table exist in MySQL?
• How can I remove every column in a table in MySQL?
• How do I remove a string from an array in a MongoDB document?