Removing a column with N foreign keys to a known other column or unknown other columns in mySQL

Question

In order to DROP a column with a fkey in mySQL, you need the name of the key, as identified in Dropping a column with a foreign key. Then, to identify a key, there's a way to do so in SQL as posted in an answer to How do I see all foreign keys to a table or column?.

I want to remove a column from a table that may have one or more foreign keys programmatically, e.g. without having to manually copy over the name of the foreign key(s) or know the name of said key(s). I don't know how many keys have been created; so the name of a particular key may be tablename_ibfk_5 in one instance and tablename_ibfk_7 in another.

More than one key may be attached to the column-to-remove, so a WHILE loop seems the most logical solution. Then it also involves creating a procedure. Here's where I got stuck: Apparently the WHILE loop refuses to function outside a procedure, while the EXISTS query will throw a nondescript syntax error when used either as a cursor or directly inside said query.

As a solution to this question, I'd accept resolving the problem with my current best attempt, or coming up with a (much) simpler way of doing something that should be straightforward: removing a column and its associated objects.

score 0 · Answer 1 · edited May 23 '17 at 11:53

This is almost, but not quite, a complete answer.

One method that seems to work for a single foreign key is from How to delete a foreign key from MySQL table dynamically?, by mixing it with the answers linked above I've produced this monstrosity:

 DELIMITER $$
DROP PROCEDURE IF EXISTS `remove_fkey_known`$$
CREATE PROCEDURE `remove_fkey_known`(IN p_table VARCHAR(100), IN p_column VARCHAR(100), IN p_dtable VARCHAR(100), IN p_dcolumn VARCHAR(100))
BEGIN
    DECLARE mySQCode VARCHAR(255);
    WHILE (SELECT COUNT(*) FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE
          WHERE
            TABLE_NAME = p_table  COLLATE utf8_unicode_ci AND
            COLUMN_NAME = p_column  COLLATE utf8_unicode_ci AND
            REFERENCED_TABLE_NAME = p_dtable  COLLATE utf8_unicode_ci AND
            REFERENCED_COLUMN_NAME = p_dcolumn  COLLATE utf8_unicode_ci) > 0 DO
        SET @mySQCode = CONCAT('ALTER TABLE `',p_table,'` DROP FOREIGN KEY `', 
        (SELECT CONSTRAINT_NAME FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
          WHERE
            TABLE_NAME = p_table  COLLATE utf8_unicode_ci AND
            COLUMN_NAME = p_column  COLLATE utf8_unicode_ci AND
            REFERENCED_TABLE_NAME = p_dtable  COLLATE utf8_unicode_ci AND
            REFERENCED_COLUMN_NAME = p_dcolumn  COLLATE utf8_unicode_ci LIMIT 1), '`');
        PREPARE stmt FROM @mySQCode;            
        EXECUTE stmt;
    END WHILE;
END$$
DELIMITER ;
CALL remove_fkey_known('srcTable', 'srcColumn', 'destTable', 'ID');

Notes:

Having to run 30 lines of code for something that seems to be doable in 1 and using all sorts of workarounds (effectively eval(), temporary procedures, INFORMATION_SCHEMA, ...) makes me wonder if a much better answer is still out there.

There is a buffer overflow with particularly long table/column names.

It's trivial to modify this procedure to remove the REFERENCED_TABLE_NAME and REFERENCED_COLUMN_NAME and their associated variables to make the method independent of the linked-to rows (a remove_fkey_unknown procedure can then be made for that).

The code above does the desired thing, but spits out a mySQL error.

#1025 - Error on rename of './[dbname]/[tablename]' to './[dbname]/#sql2-43c-3d8fab' (errno: 152)

Apparently mySQL seems to think that 0 > 0 in this case. (I've tried running the select on its own with the key removed, and the answer is 0, not NULL). Ideas?

Removing a column with N foreign keys to a known other column or unknown other columns in mySQL

1 Answers1