Drop foreign key only if it exists

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?

 Answers

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.

---

IF EXISTS(
              SELECT *
              FROM INFORMATION_SCHEMA.STATISTICS
              WHERE INDEX_SCHEMA = DATABASE()
                    AND TABLE_NAME='myTable'
                    AND INDEX_NAME = 'myIndex')
        THEN

            ALTER TABLE `myTable` DROP FOREIGN KEY `myForeignKey`;

            ALTER TABLE `myTable` DROP INDEX `myIndex` ;

        END IF;

When you create a foreign key constraint, mysql will automatically create an index 

on the referenced column. The example above shows how to check for an index in the 

INFORMATION_SCHEMA, but there is much more information for you to check out in 

the information schema. Your index name seems to indicate that it was created for a FK, 

so you'd have to drop the FK first, then drop the index. If you create the foreign key again,

 mysql will create the index again. It needs an index to enforce referential integrity without 

having to do a table scan.

If your intention was to create a new index that contains the same column, you'd have to 

create that index first (with this column, the one that will be used as a FK, being the first 

in the list of columns specified for the index). Now you can add your FK back and mysql 

will be happy to use the new index without creating another one.

Edit: to view indexes quickly simply execute SHOW INDEXES FROM myTable;

---

In the current version of Mariadb 10.1.26 (new Mysql), your query works:

Key: MUL

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

Key: <NULL>