MYSQL: ALTER TABLE DROP FOREIGN KEY based on SELECT result

Question

I have the following mysql command to reset a foreign key constraint:

ALTER TABLE network_profile DROP FOREIGN KEY 'FK98875604AC3BAD33';

But I would prefer to use the SELECT statement to find the foreign key constraint_id and use it in the ALTER TABLE command, but failed. Can this be done?

I tried:

ALTER TABLE network_profile DROP FOREIGN KEY (SELECT constraint_name FROM information_schema.key_column_usage WHERE column_name = 'mt_check_list' AND table_schema = 'mydb');

Also tried:

SELECT @constraint_name := constraint_name FROM information_schema.key_column_usage WHERE column_name = 'mt_check_list' AND table_schema = 'mydb';
ALTER TABLE network_profile DROP FOREIGN KEY @constraint_name;

Please, help?

Answer 1

Cannot use variables in an ALTER statement. You can, however, build a string, prepare an instruction from a string, and then execute the statement:

SELECT CONCAT(
    'ALTER TABLE `network_profile` DROP FOREIGN KEY `',
    constraint_name,
    '`'
) INTO @sqlst
FROM information_schema.key_column_usage
WHERE table_name = 'network_profile'
    AND column_name = 'mt_check_list';

PREPARE stmt FROM @sqlst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlst = NULL;