mysql删除表中外键,如何动态地从MySQL表中删除外键？

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

But I get an error:

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

The select alone works:

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)

解决方案

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.

I usually do something like this:

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

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.