CODEWITHSUNDEEP
mysqlforeign-keys
Brendan Crowley
Brendan Crowley

Reputation: 79

MYSQL: ALTER TABLE DROP FOREIGN KEY based on SELECT result

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?

Upvotes: 5

Views: 1678

Answers (1)

RandomSeed
RandomSeed

Reputation: 29769

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;

Upvotes: 6

Related Questions