Drop foreign key only if it exists

Question

I'm on a MySQL database.

I'm doing this, but it doesn't work.

ALTER TABLE `object` DROP FOREIGN KEY IF EXISTS `object_ibfk_1`; 

I've tried to put this IF EXISTS wherever I could. How can check if foreign key is exists before drop it?

Answer 1

If you want to drop foreign key if it exists and do not want to use procedures you can do it this way (for MySQL) :

set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE             CONSTRAINT_SCHEMA = DATABASE() AND             TABLE_NAME        = 'table_name' AND             CONSTRAINT_NAME   = 'fk_name' AND             CONSTRAINT_TYPE   = 'FOREIGN KEY') = true,'ALTER TABLE table_name             drop foreign key fk_name','select 1');  prepare stmt from @var; execute stmt; deallocate prepare stmt; 

If there is foreign key we put alter table statement in variable and if there isn't we put a dummy statement. And then we execute it.

Answer 2

For greater re-usability, you would indeed want to use a stored procedure. Run this code once on your desired DB:

   DROP PROCEDURE IF EXISTS PROC_DROP_FOREIGN_KEY;     DELIMITER $$     CREATE PROCEDURE PROC_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 ; 

Thereafter, you can always replace this:

ALTER TABLE `object` DROP FOREIGN KEY IF EXISTS `object_ibfk_1`; 

with this:

CALL PROC_DROP_FOREIGN_KEY('object', 'object_ibfk_1'); 

Your script should then run smoothly whether object_ibfk_1 actually exists or not.

A lot of credit due to: http://simpcode.blogspot.com.ng/2015/03/mysql-drop-foreign-key-if-exists.html