MYSQL: ALTER TABLE DROP FOREIGN KEY based on SELECT result

foreign-keys, mysql

Solution

It is not possible to use variables in an ALTER statement. You can, however, build a string, prepare a statement from the string, 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;

Problem

I have the following mysql command to drop a foreign key constraint: ``` ALTER TABLE network_profile DROP FOREIGN KEY 'FK98875604AC3BAD33'; ``` But i would prefer to use a SELECT statement to discover the foreign key constraint_id first and use that in the ALTER TABLE command, but have been unsuccessful. Is this possible to do? 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?

Original source