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?

回答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.

回答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

回答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;

回答4:

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

回答5:

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>

回答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;

回答7:

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.