在数据库(MySql)中删除所有外键

Question

我想重命名一张表中的一个外键列，而该列是许多表的外键。显然，只有在删除约束条件后才能实现这一点，正如我在这个链接中发现的那样。

我不想手动删除所有约束条件，有没有一种方法可以删除数据库中的所有外键约束条件？

我还尝试过SET FOREIGN_KEY_CHECKS=0;但我仍然无法重命名该列。

- Borut Flis

---

如果你不知道你在删除什么，那怎么能重新创建所有的FK呢？ - Augusto

“所有外键约束”是什么意思？您还必须考虑这些列上是否有索引。请在此处查看https://dev59.com/dXM_5IYBdhLWcg3wTRLL：它适用于SQL Server，您可能会找到类似的语法来进行更改。 - bonCodigo

只需要所有外键，我可以重新创建FKeys，因为我使用http://www.doctrine-project.org/和将重新创建表的模型。 - Borut Flis

Answer 1

您可以使用此SQL生成ALTER TABLES（需要将!!YOUR_SCHEMA_HERE!!替换为您的架构）：

您可以使用此SQL生成ALTER TABLES（需要将!!YOUR_SCHEMA_HERE!!替换为您的模式）：

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_schema='!!YOUR_SCHEMA_HERE!!';

它将生成以下SQL：

alter table `viewpoint_test`.`answer_code` DROP FOREIGN KEY fk_answer_code_codebook_item1;
alter table `viewpoint_test`.`answer_code` DROP FOREIGN KEY fk_answer_code_questionary_answer1;
alter table `viewpoint_test`.`codebook` DROP FOREIGN KEY codebook_ibfk_1;
...

“Schema name”指的是你的数据库名称。其实它们是同一个东西。

- lopisan

Answer 2

以下查询将自动构建正确的语法。 确保在WHERE条件中放入您真实的数据库架构名称。 只需执行返回的每一行，所有FOREIGN KEYS都将消失。

我把反向操作（添加它们回来）留给你作为练习。

SELECT CONCAT("alter table ", TABLE_NAME," drop foreign key ", CONSTRAINT_NAME,"; ") AS runMe
FROM information_schema.key_column_usage 
WHERE TABLE_SCHEMA='MY_SCHEMA_NAME' AND CONSTRAINT_NAME <> 'PRIMARY';

如果您需要它与模式无关，可以编写：TABLE_SCHEMA = DATABASE（）以获取当前活动的数据库名称。

- Dewey

Answer 3

你可以尝试像以下的方式使用。。。

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName);

另外，您可以尝试使用引用键。就像......

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName)
REFERENCES anotherTableName(reference_id);

- Md. Maruf Hossain

Answer 4

在找到这个线程后，我仍然遇到了一些问题，但是我似乎找到了适合我的工作流程。

数据库删除

如果您正在使用MySQL Workbench，则需要执行以下3个步骤关闭SAFE UPDATE：

1. MySql Workbench > Preferences > SQL EDITOR >（设置底部）
2. 关闭SAFE UPDATE
3. 然后注销连接并重新连接

开始清除数据库并启动新的步骤

1. 从表中删除所有数据

（此MySQL脚本将为数据库中的每个表创建一个DELETE FROM脚本。）

SELECT concat('DELETE FROM ',table_schema,'.',table_name,';') FROM information_schema.table_constraints WHERE table_schema='!!TABLE_SCHEMA!!';

复制此输出并运行它。您可能需要逐行执行此操作。

2. 从表中删除所有外键

（此MySQL脚本将为数据库中的每个表创建一个ALTER TABLE _ DROP FOREIGN KEY脚本。）

从information_schema.table_constraints WHERE constraint_type='FOREIGN KEY' AND table_schema='!!TABLE_SCHEMA!!';选择concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';')

将输出内容复制并运行。你可能需要逐行执行此操作。

完成此操作后，您应该能够运行DROP DATABASE脚本。

- MaylorTaylor

Answer 5

执行以下查询

select * from information_schema.key_column_usage

将向您展示数据库中存在的所有约束（包括列名、约束类型、表和模式）。您会注意到这些列：

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

接下来，如果您计划删除引用您列的每个约束，则应考虑REFERENCED_*列并运行类似以下的内容：

DELETE FROM information_schema.key_column_usage 
WHERE 
    REFERENCED_TABLE_SCHEMA='myschema'
    AND
    REFERENCED_TABLE_NAME='mytable'
    AND
    REFERENCED_COLUMN_NAME='mycolumn'

http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

- Augusto

---

很遗憾，这不会起作用，因为 information_schema 表不可写。 - SystemParadox

这种方法（即使用户有权限，实际上并没有）只会在许多表中留下大量垃圾值。这个解决方案根本不能胜任。 - Guilherme Ferreira