在MySql中删除未命名的外键

Question

如果外键是在没有名称的情况下创建的，MySql会给它一个默认名称。例如，对于表'Test‘，外键将被命名为'test_ibfk_1’。当我使用这个名称在本地删除外键时，它的工作方式很不错，但是在开发服务器上失败，出现errno: 152。

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

我的问题是:依赖默认名称操作约束(至少在MySql中)安全吗？

提前感谢！

Answer 1

您需要知道外键的名称。如果它是在没有名称的情况下创建的，则会自动生成名称。您应该获得有关外键的信息。

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

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使用ALTER TABLE <table_name> DROP INDEX <fk_name>;丢弃外键。

Answer 2

匆忙的读者，不会因为这个答案太长而放弃这个答案。你不会找到另一个解决方案，，我保证:)

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

-- 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;

Answer 3

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

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;

在这种情况下，您不需要知道外键的名称，只需要要删除外键的table_name、数据库名称和引用表。