在MySql中删除未命名的外键

Question

如果没有指定名字，外键在创建时MySQL会自动为其命名一个默认名称。例如，对于表'Test'的外键将被命名为'test_ibfk_1'。当我使用此名称在本地删除外键时，它能正常工作，但在开发服务器上却失败并出现错误号：152。

我知道这个名称是大小写敏感的，但无论是小写还是大写结果都是相同的。

我的问题是：在操作约束（至少在MySQL中）时，依赖默认名称是否安全？

提前感谢您！

- Ariel Chelsău

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;

使用ALTER TABLE <table_name> DROP INDEX <fk_name>;命令删除外键。

- Devart

---

以上查询适用于非管理员吗？总的来说，非常好的知识！谢谢！ - Ariel Chelsău

为了获取这种类型查询的 fk_name，必须经过这种耐力考验真是太遗憾了。本来应该很简单就能说出：“ALTER table table_name DROP CONSTRAINT ON col_name”（不管它叫什么名字）。毕竟，在给定键上只能有一个这样的约束。对吧，大家？ - Ifedi Okonkwo

@IfediOkonkwo，我完全同意。如果你正在使用像Liquibase这样的工具来管理跨多个环境的数据库模式更改，那么这将是一个特别棘手的问题，因为不能保证在每个运行它们的数据库上生成相同的FK约束名。这意味着您不能依赖该约束名称进行未来的更改。尽管您可以从一开始就使用固定名称来命名约束。 - Jeff Evans

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;

- mircealungu

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;

这样的话，您不需要知道外键的名称，只需要知道想要删除外键的表名、数据库名称和参考表即可。

- jose miguel rivera rodríguez

Answer 4

如果有人对如何从表中删除所有外键感兴趣，我们在更新存储过程中使用以下代码（示例应用于我们内部数据库的db_Order表）：

DECLARE counter INT DEFAULT 1;
DECLARE foreignKeyDeleteStatement VARCHAR(200) DEFAULT '';

...

SET counter = (SELECT COUNT(1) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND table_schema=DATABASE() AND table_name='db_Order');
    SELECT 'Number of foreignKeys on table db_Order = ' + counter;

WHILE counter > 0 DO
                SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') 
                FROM information_schema.key_column_usage 
                WHERE CONSTRAINT_SCHEMA = DATABASE()
                AND TABLE_NAME='db_order' 
                AND REFERENCED_TABLE_NAME IS NOT NULL
                LIMIT 1
                INTO foreignKeyDeleteStatement;
                
                SELECT foreignKeyDeleteStatement;
                
                SET @s = foreignKeyDeleteStatement;
        
                PREPARE stmt1 FROM @s;
                EXECUTE stmt1;
                DEALLOCATE PREPARE stmt1;
                
                SET counter = counter - 1;
END WHILE;

希望这对某人有用。

- Cristina Alboni