MySQL 仅当外键存在时才删除外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17161496/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop foreign key only if it exists
提问by Creditto
I'm on a MySQL database.
我在 MySQL 数据库上。
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?
我试图把这个 IF EXISTS 放在任何可能的地方。如何在删除前检查外键是否存在？
回答by NikolaB
If you want to drop foreign key if it exists and do not want to use proceduresyou can do it this way (for MySQL) :
如果您想删除存在的外键并且不想使用过程，您可以这样做（对于 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.
如果有外键，我们将 alter table 语句放在变量中，如果没有，我们将放置一个虚拟语句。然后我们执行它。
回答by Ifedi Okonkwo
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_1actually exists or not.
无论是否object_ibfk_1实际存在，您的脚本都应该顺利运行。
A lot of credit due to: http://simpcode.blogspot.com.ng/2015/03/mysql-drop-foreign-key-if-exists.html
很多功劳归功于：http: //simpcode.blogspot.com.ng/2015/03/mysql-drop-foreign-key-if-exists.html
回答by heruka
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.
创建外键约束时，mysql 会自动在引用的列上创建索引。上面的示例显示了如何检查 INFORMATION_SCHEMA 中的索引，但您可以在信息架构中查看更多信息。您的索引名称似乎表明它是为 FK 创建的，因此您必须先删除 FK，然后再删除索引。如果再次创建外键，mysql 将再次创建索引。它需要一个索引来强制引用完整性，而不必进行表扫描。
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.
如果您打算创建一个包含相同列的新索引，则必须首先创建该索引（对于此列，将用作 FK 的列是为该列指定的列列表中的第一个指数）。现在您可以重新添加您的 FK，mysql 将很乐意使用新索引而无需创建另一个索引。
Edit: to view indexes quickly simply execute SHOW INDEXES FROM myTable;
编辑：要快速查看索引，只需执行 SHOW INDEXES FROM myTable;
回答by IntCom
In the current version of Mariadb 10.1.26 (new Mysql), your query works:
在 Mariadb 10.1.26（新 Mysql）的当前版本中，您的查询有效：
Key:
MUL
钥匙：
MUL
ALTER TABLE `object` DROP FOREIGN KEY IF EXISTS `object_ibfk_1`;
DESC `object`;
Key:
<NULL>
钥匙：
<NULL>
回答by automatix
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之前的 MySQL 和 MariaDB 版本中缺少的v10.1.4. 您也可以将它用于您想要的所有其他语句，这应该取决于 an 的存在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;
回答by Hyman Daniel's
Which Database you are using??
你用的是哪个数据库？？
If SQL Server
如果 SQL 服务器
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
回答by Skylar
Similar discussion: How do I drop a foreign key constraint only if it exists in sql server?
类似讨论： 如何仅在sql server中存在外键约束时才删除它？
IF (OBJECT_ID('FK_ConstraintName', 'F') IS NOT NULL)
is very useful and not mentioned yet here.
非常有用，这里还没有提到。
