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

您可以使用DROP INDEX。其语法如下 –

alter table 你的表名 drop index 你的唯一索引名称;

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

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

现在，使用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`;
Query OK, 0 rows affected (0.25 sec)
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)

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

阅读更多：MySQL 教程