How to delete a foreign key from MySQL table dynamically?

Question

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)

RC. · Accepted Answer · 2012-02-28T14:30:59.823

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.

score 2 · Answer 2 · answered Feb 28 '12 at 14:36

I don't have mySQL to hand so can't test this, but I think something along the lines of the following will work:

DECLARE @SQL VARCHAR(100)

SELECT  @SQL = 'alter table tblTableWhereFKIs drop foreign key ' + constraint_name
FROM    information_schema.key_column_usage
WHERE   referenced_table_name = 'tblReferencedByFK' 
AND     table_name = 'tblTableWhereFKIs'

PREPARE stmt FROM @SQL
EXECUTE stmt

My experience of MySQL is limited so this is a mixture of your answer and information from the MySQL Website

How to delete a foreign key from MySQL table dynamically?

2 Answers2

Linked