How to drop foreign key using a subselect?

December 7, 2023

First off, we’re using MySQL 5.

I’d like to do something like the following:

SET @fkcn = (SELECT CONSTRAINT_NAME
             FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
             WHERE REFERENCED_TABLE_SCHEMA = (SELECT DATABASE())
               AND REFERENCED_TABLE_NAME = "ProviderOrder");
ALTER TABLE AuditProviderOrder
    DROP FOREIGN KEY @fkcn,
    DROP COLUMN ProviderOrderID;

But MySQL 5 complains that it’s invalid syntax. I’ve tried DROP FOREIGN KEY (SELECT @fkcn) as well as inlining @fkcn with similar syntax issues.

Is there a way to fully automating retrieving the foreign key name for use in the DROP FOREIGN KEY clause?

>Solution :

You have to put the entire DROP statement into a string that you execute with PREPARE and EXECUTE.

SET @drop = CONCAT('ALTER TABLE AuditProviderOrder
    DROP FOREIGN KEY ', @fkcn, ',
    DROP COLUMN ProviderOrderID');
PREPARE stmt FROM @drop;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;