在MYSQL数据库中删除所有外键

Question

外键让我在修改数据库结构以满足新需求时遇到了太多问题 - 我想要修改主键，但当外键引用该表时似乎无法修改（我认为这是因为MySQL会删除并重新创建该表）。

因此，在我修改数据库的同时，我想简单地删除所有外键，并稍后重新创建它们。有没有一种简洁的方法可以实现这个目的？

- Mr. Boy

---

只是一个未经测试的想法，但将表从Innodb更改为MyISAM怎么样？尽管转换回来可能会很困难... - Sablefoste

Answer 1

运行

SELECT concat('ALTER TABLE `', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;') 
FROM information_schema.key_column_usage 
WHERE CONSTRAINT_SCHEMA = 'db_name' 
AND referenced_table_name IS NOT NULL;

并运行输出。

- Zoozy

---

我们如何运行输出？例如，在脚本中。 - Lightness Races in Orbit

我们如何将输出重定向到一个SQL文件，并从SQL语句中运行该文件？ - Lightness Races in Orbit

我知道现在有点晚了，但是从MySQL Workbench工作时，我只需要运行上面的查询，然后将输出复制粘贴到一个新脚本中并运行该脚本。这就是我“将输出重定向到SQL文件”的方法。 - Zack Knopp

这对我很有帮助。为了补充这个答案，我的结果中出现了一些语句出现了两次或更多次，所以我在字段中添加了 AS statement ，并在 SELECT 语句的末尾添加了 GROUP BY statement。 - mikl

在 SELECT 后添加 DISTINCT 关键字，例如 SELECT DISTINCT concat(... 将会移除重复的 ALTER TABLE 语法。 - Aryo

Answer 2

你可以在进行任何Alter Table语句之前，简单地执行以下命令：

你可以在进行任何Alter Table语句之前，简单地执行以下命令：

SET foreign_key_checks = 0;

这将关闭你的数据库连接中的外键约束检查。然后，您可以进行更改而无需担心约束。

完成后，请不要忘记发出以下命令：

SET foreign_key_checks = 1;

要重新启用它们。

请注意，这仍然不允许您创建新的外键约束，因为列数据类型不匹配而失败。

- Mike Brant

Answer 3

另一个版本的Zoozy代码，在这里你只能选择一个表：

SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') 
FROM information_schema.key_column_usage 
WHERE CONSTRAINT_SCHEMA = 'YOUR DB HERE' 
AND TABLE_NAME='YOUR TABLE HERE' 
AND REFERENCED_TABLE_NAME IS NOT NULL;

同时使用一个过程：

DROP PROCEDURE IF EXISTS dropForeignKeysFromTable;

delimiter ///
create procedure dropForeignKeysFromTable(IN param_table_schema varchar(255), IN param_table_name varchar(255))
begin
    declare done int default FALSE;
    declare dropCommand varchar(255);
    declare dropCur cursor for 
        select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name, ';') 
        from information_schema.table_constraints
        where constraint_type='FOREIGN KEY' 
            and table_name = param_table_name
            and table_schema = param_table_schema;

    declare continue handler for not found set done = true;

    open dropCur;

    read_loop: loop
        fetch dropCur into dropCommand;
        if done then
            leave read_loop;
        end if;

        set @sdropCommand = dropCommand;

        prepare dropClientUpdateKeyStmt from @sdropCommand;

        execute dropClientUpdateKeyStmt;

        deallocate prepare dropClientUpdateKeyStmt;
    end loop;

    close dropCur;
end///

- Grégor González

Answer 4

如果您想在多个数据库中使用相同的表格，请不要忘记在输出中添加CONSTRAINT_SCHEMA。

SELECT concat('ALTER TABLE ', CONSTRAINT_SCHEMA,'.',TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA like 'your_db_prefix_%'
    AND TABLE_NAME='your_table'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

- mpen