在MySql中删除未命名的外键 [英] Drop unnamed Foreign Key in MySql

问题描述

如果创建的外键没有名称，则MySql将为其提供默认值.例如，对于表"Test"，外键将被命名为"test_ibfk_1".当我使用此名称在本地放置外键时，它像一个超级按钮一样工作，但在开发服务器上却以errno失败:152.

If a foreign key was created without a name, MySql will give it a default one. For instance, for table 'Test' the foreign key will be named 'test_ibfk_1'. When I drop the foreign key locally by using this name it works like a charm but on the development server it fails with errno: 152.

我知道这个名称区分大小写，但是无论大小写，结果都是相同的.

I know this name is case sensitive but either lower or upper case the result is the same.

我的问题:依靠默认名称来操纵约束(至少在MySql中)安全吗?

My question: Is it safe to rely on the default name to manipulate constraints (in MySql at least)?

提前谢谢！

推荐答案

您需要知道外键的名称.如果创建时没有名称，则名称将自动生成.您应该获取有关外键的信息.

You need to know the name of foreign key. If it was created without name, then name will be autogenerated. You should get information about the foreign key.

使用以下查询之一获取外键名称-

Use one of these queries to get foreign key names -

SELECT
  constraint_name
FROM
  information_schema.REFERENTIAL_CONSTRAINTS
WHERE
  constraint_schema = <'db_name'> AND table_name = <'table_name'>;


SELECT *
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  constraint_schema = <'db_name'> AND table_name = <'table_name'> AND   
  referenced_table_name IS NOT NULL;

...并使用 ALTER TABLE< table_name>DROP INDEX< fk_name> ;; 来删除外键.

...and use ALTER TABLE <table_name> DROP INDEX <fk_name>; to drop foreign key.

这篇关于在MySql中删除未命名的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！