DavidR DavidR - 2 months ago 5
MySQL Question

MySQL remove Foreign Key without name

I'm trying to create a script that will remove a foreign key constraint without a set name from several different MySQL DB's. I have created the following script:

SET @CN := (SELECT CONSTRAINT_NAME
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
table_name = 'inventory' and referenced_table_name='product_code' and referenced_column_name='shipping_code');

ALTER TABLE inventory DROP FOREIGN KEY @CN;


It is correctly getting the contraint name. However it complains about the variable
@CN
in the ALTER TABLE command. Why doesn't this work?

Answer

MySQL is expecting a fixed name there. So, just use dynamic SQL:

set @sql = 'ALTER TABLE inventory DROP FOREIGN KEY @CN';
set @sql = replace(@sql, '@CN', @CN);

prepare alterTable from @sql;
execute alterTable;
Comments