仅在存在的情况下删除外键

Question

我正在使用MySQL数据库。

我正在进行这个操作，但它不起作用。

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

我已经尽可能在所有地方添加了IF EXISTS。如何在删除之前检查外键是否存在？

- Thadeuse

---

如果您的目标只是暂时禁用外键，请在您的代码之前使用 SET FOREIGN_KEY_CHECKS=0;，并在之后使用 SET FOREIGN_KEY_CHECKS=1;，而不是必须删除它。 - Danny Beckett

这是一个功能请求，希望将DROP FOREIGN KEY IF EXISTS添加到MySQL bugs.mysql.com/bug.php?id=5746中，但被关闭为Won't Fix。 - Sergey Ponomarev

Answer 1

如果您想删除外键（如果存在）并且不想使用存储过程，可以通过以下方式进行操作（适用于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;

如果存在外键，我们将修改表语句放入变量中，如果没有，则放入虚拟语句。然后我们执行它。

- NikolaB

Answer 2

为了更好的可重用性，您确实需要使用存储过程。在所需的数据库上运行此代码：

   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 ;

此后，您始终可以替换这个：

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

有了这个：

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

你的脚本应该可以顺利运行，无论object_ibfk_1是否存在。

感谢：http://simpcode.blogspot.com.ng/2015/03/mysql-drop-foreign-key-if-exists.html提供的大量帮助。

- Ifedi Okonkwo

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;

当您创建外键约束时，MySQL会自动在参考列上创建一个索引。上面的示例显示了如何在INFORMATION_SCHEMA中检查索引，但是在information schema中还有更多信息可供查看。您的索引名称似乎表明它是为FK创建的，因此您需要先删除FK，然后再删除索引。如果您重新创建外键，则MySQL会再次创建索引。它需要索引来强制执行引用完整性而无需执行表扫描。

如果您的意图是创建包含相同列的新索引，则首先必须创建该索引（将用作FK的列首先列在指定索引列列表中）。现在您可以添加FK，MySQL将高兴地使用新索引而不创建另一个索引。

编辑：要快速查看索引，只需执行SHOW INDEXES FROM myTable;

- heruka

Answer 4

在当前版本的Mariadb 10.1.26（新的Mysql）中，你的查询可以使用：

关键字： MUL

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

关键字： <NULL>

- IntCom

Answer 5

这里提供了一个解决方案，用于处理在MySQL和MariaDB版本中缺失的DROP FOREIGN KEY IF EXISTS问题，该问题出现在v10.1.4之前。您还可以将其用于其他需要依赖于FOREIGN KEY存在的语句（例如，在下面的示例中使用SELECT "info: foreign key exists."）。

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

- automatix

---

指出此代码功能中“在MySQL和MariaDB v10.1.4之前版本中缺失”的方面将有所帮助。 - Ifedi Okonkwo

Answer 6

你使用哪个数据库？？

如果是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

- Jack Daniel's

---

我正在使用MYSQL，我已经尝试在任何可能的地方放置了IF EXISTS。如何在删除之前检查外键是否存在？ - Thadeuse

你正在使用哪个MySQL存储引擎？MYISAM还是INNODB？你确定你没有使用MYISAM吗？ - Jack Daniel's

我在MySQL中使用InnoDB存储引擎，已经尝试在可能的情况下添加了IF EXISTS。如何在删除外键之前检查其是否存在？ - Thadeuse

Answer 7

类似讨论： 如何在SQL Server中仅在存在的情况下删除外键约束？

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

非常有用，而且在这里还没有提到。

- Skylar