simpcode: MySQL Drop Foreign Key If Exists

If you tried to drop a foreign key from a table, and the foreign key does not exist, you may hit to error such as:

Error Code: 1025
Error on rename of '.\mydatabase\mytable' to '.\mydatabase\#sql2-xxxx-xx' (errno: 152)

This may not be an issue if the statement is executed manually, but it's troublesome if the statement is part of a larger script that runs in batch (without using the --force option).

Currently there is no way to check for "IF EXISTS" before dropping the foreign key. However, you could create a temporary procedure to achieve that:

/* Purpose: Drop foreign key if exists, without generating errors.
 * Usage  : Set in the 'Modify - Begin/End' section below, then include this script into the real script.
*/

DROP PROCEDURE IF EXISTS tmp_drop_foreign_key;

DELIMITER $$

CREATE PROCEDURE tmp_drop_foreign_key(IN tableName VARCHAR(64), IN constraintName VARCHAR(64))
BEGIN
    IF EXISTS(
        SELECT * FROM information_schema.table_constraints
        WHERE 
            table_schema    = DATABASE()     AND
            table_name      = tableName      AND
            constraint_name = constraintName AND
            constraint_type = 'FOREIGN KEY')
    THEN
        SET @query = CONCAT('ALTER TABLE ', tableName, ' DROP FOREIGN KEY ', constraintName, ';');
        PREPARE stmt FROM @query; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt; 
    END IF; 
END$$

DELIMITER ;

/* ========= Modify - Begin. ========= */
CALL tmp_drop_foreign_key('table1', 'foreign_key_1');
-- Add CALL statements for any other tables and foreign keys here.
/* ========= Modify - End. =========== */

DROP PROCEDURE tmp_drop_foreign_key;