如何从 MySQL 表中删除唯一性约束?
mysqlmysqli database
您可以使用 DROP INDEX 来实现此目的。语法如下 −
alter table yourTablename drop index yourUniqueName;
为了理解上述语法，让我们创建一个表。创建表的查询如下 −
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)
查看上面的示例输出，没有唯一性约束。
