如何从MySQL表中删除唯一性约束？

您可以为此使用DROP INDEX。语法如下-

alter table yourTablename drop index yourUniqueName;

为了理解上述语法，让我们创建一个表。创建表的查询如下-

mysql> create table removeUniquenessConstraint
   -> (
   -> Id int,
   -> Name varchar(100),
   -> Age int,
   -> isGreaterThan18 bool,
   -> UNIQUE(Id,isGreaterThan18)
   -> );

现在，借助SHOW CREATE命令检查表的详细信息。查询如下-

mysql> show create table removeUniquenessConstraint;

这是输出-

+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

以下是从MySQL表中删除唯一性约束的查询-

mysql> alter table removeUniquenessConstraint drop index `Id`;
Records: 0 Duplicates: 0 Warnings: 0

让我们再次检查表的所有详细信息。查询如下-

mysql> show create table removeUniquenessConstraint;

这是输出-

+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

查看上面的示例输出，没有唯一性约束。