CODEWITHSUNDEEP
mysql
DavidR
DavidR

Reputation: 6962

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?

Upvotes: 4

Views: 1101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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;

Upvotes: 6

Related Questions