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?

Aleppo

If your goal is just to temporarily disable a foreign key, use SET FOREIGN_KEY_CHECKS=0; before your code and SET FOREIGN_KEY_CHECKS=1; afterwards, instead of having to drop it. – Sweetener

Here is a feature request to add DROP FOREIGN KEY IF EXISTS into MySQL bugs.mysql.com/bug.php?id=5746 but it was closed as Won't Fix – Halloween

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.

Rommel

Nice, but what's up with the prepared statement? What's that for? – Lamonica

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

Zymogenesis

Answer 3

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;

Applicant

Answer 4

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>

Cynara

Question was not regarding MariaDB – Sevastopol

Answer 5

Which Database you are using??

If SQL　Server

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FKName]') AND      
parent_object_id = OBJECT_ID('TableName'))
alter table TableName drop constraint FKName

Sears

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

Which mysql storage engine you are using? MYISAM or INNODB?? You sure you are not using MYISAM? – Cowskin

AM using INNODB storage engine in mysql ,I've tried to put this IF EXISTS wherever I could. How can check if foreign key is exists before drop it? – Aleppo

Answer 6

Here is a workaround for the DROP FOREIGN KEY IF EXISTS, that is missing in MySQL and MariaDB versions before v10.1.4. You can also use it for every other statement you want, that should be depend on the existence of an FOREIGN KEY (e.g. for SELECT "info: foreign key exists." like in the example below).

-- DROP FOREIGN KEY IF EXISTS
SELECT
    COUNT(*)
INTO
    @FOREIGN_KEY_my_foreign_key_ON_TABLE_my_table_EXISTS
FROM
    `information_schema`.`table_constraints`
WHERE
    `table_schema` = 'my_database'
    AND `table_name` = 'my_table'
    AND `constraint_name` = 'my_foreign_key'
    AND `constraint_type` = 'FOREIGN KEY'
;
-- SELECT @FOREIGN_KEY_my_foreign_key_ON_TABLE_my_table_EXISTS;
SET @statement := IF(
    @FOREIGN_KEY_my_foreign_key_ON_TABLE_my_table_EXISTS > 0,
    -- 'SELECT "info: foreign key exists."',
    'ALTER TABLE my_table DROP FOREIGN KEY my_foreign_key',
    'SELECT "info: foreign key does not exist."'
);
PREPARE statement FROM @statement;
EXECUTE statement;

Hydrophilous

It would help to point out aspects the functionality of this code that are "missing in MySQL and MariaDB versions before v10.1.4" – Zymogenesis

Answer 7

Did not find exactly what I wanted here, so here's my version (which is a combination of other's):

DELIMITER // -- only for UI tools, like HeidiSQL

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
           WHERE TABLE_SCHEMA = DATABASE() 
           AND CONSTRAINT_TYPE = 'FOREIGN KEY' 
           AND CONSTRAINT_NAME = 'fKname')
THEN 
    ALTER TABLE `tableName` DROP FOREIGN KEY `fKname`;
END IF;

Lamonica

Answer 8

Similar discussion: How do I drop a foreign key constraint only if it exists in sql server?

IF (OBJECT_ID('FK_ConstraintName', 'F') IS NOT NULL)

is very useful and not mentioned yet here.

Incongruent