Question 299879 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to drop foreign key using a subselect?

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    You can’t directly use a variable in the DROP FOREIGN KEY, but you can construct a dynamic sql statement and execute it.

    SET @fkcn = (SELECT CONSTRAINT_NAME
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE REFERENCED_TABLE_SCHEMA = (SELECT DATABASE())
              AND REFERENCED_TABLE_NAME = "ProviderOrder");

SET @sql = CONCAT('ALTER TABLE AuditProviderOrder DROP FOREIGN KEY ', @fkcn);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search