如何从我的 MySQL 表中删除约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14122031/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove constraints from my MySQL table?
提问by deepu sankar
I want to remove constraints from my table. My query is:
我想从我的表中删除约束。我的查询是：
ALTER TABLE `tbl_magazine_issue`
DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users`
But I got an error:
但我得到了一个错误：
#1064- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraintFK_tbl_magazine_issue_mst_users' at line 1
#1064- 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解FK_tbl_magazine_issue_mst_users在第 1 行的“约束”附近使用的正确语法
回答by Bohemian
Mysql has a special syntax for dropping foreign key constraints:
Mysql 有一个特殊的语法来删除外键约束：
ALTER TABLE tbl_magazine_issue
DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users
回答by Wellington Lorindo
I had the same problem and I got to solve with this code:
我遇到了同样的问题，我必须用以下代码解决：
ALTER TABLE `table_name` DROP FOREIGN KEY `id_name_fk`;
ALTER TABLE `table_name` DROP INDEX `id_name_fk`;
回答by Lothar
There is no such thing as DROP CONSTRAINTin MySQL. In your case you could use DROP FOREIGN KEYinstead.
DROP CONSTRAINT在 MySQL 中没有这样的东西 。在您的情况下，您可以DROP FOREIGN KEY改用。
回答by Robert Knight
If the constraint is not a foreign key, eg. one added using 'UNIQUE CONSTRAINT (colA, colB)' then it is an index that can be dropped using ALTER TABLE ... DROP INDEX ...
如果约束不是外键，例如。使用 'UNIQUE CONSTRAINT (colA, colB)' 添加一个索引，然后它是一个可以使用删除的索引ALTER TABLE ... DROP INDEX ...
回答by roelleor
Also nice, you can temporarily disable all foreign key checks from a mysql database:
SET FOREIGN_KEY_CHECKS=0;
And to enable it again:
SET FOREIGN_KEY_CHECKS=1;
也不错，您可以暂时禁用 mysql 数据库中的所有外键检查：
SET FOREIGN_KEY_CHECKS=0;
并再次启用它：
SET FOREIGN_KEY_CHECKS=1;
回答by jbobbins
To add a little to Robert Knight's answer, since the title of the post itself doesn't mention foreign keys (and since his doesn't have complete code samples and since SO's comment code blocks don't show as well as the answers' code blocks), I'll add this for uniqueconstraints. Either of these work to drop the constraint:
为 Robert Knight 的答案添加一点，因为帖子的标题本身没有提到外键（并且因为他没有完整的代码示例，并且因为 SO 的评论代码块没有像答案的代码那样显示）块），我会为唯一约束添加它。这些工作中的任何一个都可以删除约束：
ALTER TABLE `table_name` DROP KEY `uc_name`;
or
或者
ALTER TABLE `table_name` DROP INDEX `uc_name`;
回答by piscator
Some ORM's or frameworks use a different naming convention for foreign keys than the default FK_[parent table]_[referenced table]_[referencing field], because they can be altered.
某些 ORM 或框架对外键使用与默认命名不同的命名约定FK_[parent table]_[referenced table]_[referencing field]，因为它们可以更改。
Laravel for example uses [parent table]_[referencing field]_foreignas naming convention. You can show the names of the foreign keys by using this query, as shown here:
例如，Laravel[parent table]_[referencing field]_foreign用作命名约定。您可以使用此查询显示外键的名称，如图所示在这里：
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = '<database>' AND REFERENCED_TABLE_NAME = '<table>';
Then remove the foreign key by running the before mentioned DROP FOREIGN KEY query and its proper name.
然后通过运行前面提到的 DROP FOREIGN KEY 查询及其专有名称来删除外键。
回答by Markus Barthlen
For those that come here using MariaDB:
对于那些使用 MariaDB 来到这里的人：
Note that MariaDB allows DROP CONSTRAINT statements in general, for example for dropping check constraints:
请注意，MariaDB 通常允许 DROP CONSTRAINT 语句，例如用于删除检查约束：
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
回答by Akshay Sharma
- Go to structure view of the table
- You will see 2 option at top a.Table structure b.Relation view.
- Now click on Relation view, here you can drop your foreign key constraint. You will get all relation here.
- 转到表的结构视图
- 您将在顶部看到 2 个选项 a.Table 结构 b。关系视图。
- 现在点击Relation view，在这里你可以删除你的外键约束。你会在这里得到所有的关系。
回答by Youness HARDI
There is no DROP CONSTRAINTIn MySql. This work like magic in mysql 5.7
DROP CONSTRAINTMySql 中没有。这在 mysql 5.7 中就像魔术一样工作
ALTER TABLE answer DROP KEY const_name;
