在MySql中删除未命名的外键

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.

如果创建的外键没有名称，MySql将为其提供默认值。例如，对于表'Test'，外键将命名为'test_ibfk_1'。当我使用此名称在本地删除外键时，它就像一个魅力，但在开发服务器上它失败了errno：152。

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

我知道这个名字区分大小写，但无论是大写还是大写，结果都是一样的。

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

我的问题：依靠默认名称来操纵约束是否安全（至少在MySql中）？

Thanks in advance!

提前致谢！

3 个解决方案

#1

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;

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

...并使用ALTER TABLE DROP INDEX ;丢掉外键。

#2

You could remove without know the name, creating a concatenate query and executing automatically:

您可以在不知道名称的情况下删除，创建连接查询并自动执行：

set @s:='';
select @s:=concat(@s, 'alter table ', 'your_table', ' drop foreign key ',CONSTRAINT_NAME, ';')
from information_schema.key_column_usage
where CONSTRAINT_SCHEMA = 'your_database'
  and TABLE_NAME ='your_table'
  and REFERENCED_TABLE_NAME = 'the_foreign_reference_table';
prepare stmt from @s;
execute stmt;

deallocate prepare stmt;

I that way you don't need to know the name of the foreign key, only need the table_name which you want to remove the foreign key, the database name and the reference table.

我那样你不需要知道外键的名称，只需要你要删除外键的table_name，数据库名和引用表。

#3

Hurried reader, don't go away from this answer, just because it's long. You won't find another solution, I promise :)

匆匆的读者，不要忘记这个答案，只因为它很长。你不会找到另一个解决方案，我保证:)

The accepted answer does not drop the key, it only finds its name. To actually drop the key with an unknown name, you'll need to use prepared statements. The most general solution is this script that you can customize with five variables:

接受的答案不会丢弃密钥，它只会找到它的名字。要实际删除具有未知名称的密钥，您需要使用预准备语句。最通用的解决方案是这个脚本，您可以使用五个变量进行自定义：

-- YOU MUST SPECIFY THESE VARIABLES TO FULLY IDENTIFY A CONSTRAINT
SET @table_name = '...';
SET @column_name = '...';
SET @referenced_table_name = '...';
SET @referenced_column_name = '...';
-- make sure to limit queries to a single db schema
SET @db_name = '...'; 

-- find the name of the foreign key and store it in a var
SET @constraint_name = (
    SELECT constraint_name
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = @table_name
        AND COLUMN_NAME = @column_name
        AND CONSTRAINT_SCHEMA = @db_name
        AND referenced_table_name = @referenced_table_name
        AND referenced_column_name = @referenced_column_name);


-- prepare the drop statement in a string and run it
SET @s = concat('alter table ', @table_name, ' drop foreign key ', @constraint_name);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#1