如何动态地从MySQL表中删除外键? [英] How to delete a foreign key from MySQL table dynamically?

问题描述

我正在尝试从引用指定表的表中删除外键.我不知道外键的名称，我只知道它所在的表及其引用的表.这是我到目前为止所得到的:

I am trying to drop a foreign key from a table referencing a specified table. I don't know the name of the foreign key, I only know the table it is in and the table it references. This is what I got so far:

alter table tblTableWhereFKIs drop foreign key (select constraint_name 
from information_schema.key_column_usage 
where referenced_table_name = 'tblReferencedByFK' and table_name = 'tblTableWhereFKIs' limit 1);

但是我得到一个错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select constraint_name
from information_schema.key_column_usage
where referen' at line 1

仅选择一项有效:

mysql> select constraint_name
    -> from information_schema.key_column_usage
    -> where referenced_table_name = 'tblReferencedByFK' and table_name = 'tblTableWhereFKIs' limit 1;
+-----------------------------------------+
| constraint_name                         |
+-----------------------------------------+
| fk_tblTableWhereFKIs_tblReferencedByFK1 |
+-----------------------------------------+
1 row in set (0.08 sec)

推荐答案

我不相信您可以这样做. alter语句不知道如何将您选择的结果外推到多次执行drop外键.

I don't believe you can do that. The alter statement doesn't know how to extrapolate the results from your select into multiple executions of drop foreign key.

我通常会这样:

SELECT CONCAT('alter table ', table_name, ' drop foreign key ', constraint_name, ';')
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'tblReferencedByFK' and table_name = 'tblTableWhereFKIs';

我执行上面的查询，它将为我构建所有的alter语句.然后，我获取该alter语句列表并手动运行它们.

I execute the above query which will build all the alter statements for me. I then take that list of alter statements and run them manually.

这篇关于如何动态地从MySQL表中删除外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！