I am attempting to write a MySQL statement to delete a foreign key constraint from a table, however I will not be provided the name for the constraint. I will be provided the table and column names, which I know I can then use by searching in the information_schema.KEY_COLUMN_USAGE table to retrieve the constraint name. Currently, I have the following statement, which is obviously incorrect, but I can't remember how I would accomplish what I want to do:

ALTER TABLE [table] DROP FOREIGN KEY (SELECT CONSTRAINT_NAME foreignKey FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = '[table]' AND COLUMN_NAME = '[column]' AND REFERENCED_TABLE_NAME IS NOT NULL LIMIT 1);

CodePudding user response：

You can't fetch the name of the constraint as a string from the information_schema, and then use it as identifier in your alter table statement in the same query. You must do this as two separate queries.

First, fetch the name of the constraint, as you do in the subquery you show.

Then, format a second SQL statement, the ALTER TABLE, using the constraint name as part of that SQL statement. All identifiers must be part of the SQL query string before it is parsed.