如何在 MySQL 中临时禁用外键约束？

How to temporarily disable a foreign key constraint in MySQL?

提问by jul

Is it possible to temporarily disable constraints in MySQL?

是否可以暂时禁用 MySQL 中的约束？

I have two Django models, each with a ForeignKey to the other one. Deleting instances of a model returns an error because of the ForeignKey constraint:

我有两个 Django 模型，每个模型都有一个外键到另一个。由于 ForeignKey 约束，删除模型的实例会返回错误：

cursor.execute("DELETE FROM myapp_item WHERE n = %s", n)
transaction.commit_unless_managed()  #a foreign key constraint fails here

cursor.execute("DELETE FROM myapp_style WHERE n = %s", n)
transaction.commit_unless_managed()

Is it possible to temporarily disable constraints and delete anyway?

是否可以暂时禁用约束并删除？

回答by Andrew Campbell

Try DISABLE KEYSor

尝试DISABLE KEYS或

SET FOREIGN_KEY_CHECKS=0;

make sure to

确保

SET FOREIGN_KEY_CHECKS=1;

after.

后。

回答by berniey

To turn off foreign key constraint globally, do the following:

要全局关闭外键约束，请执行以下操作：

SET GLOBAL FOREIGN_KEY_CHECKS=0;

and remember to set it back when you are done

完成后记得把它放回去

SET GLOBAL FOREIGN_KEY_CHECKS=1;

WARNING: You should only do this when you are doing single user mode maintenance. As it might resulted in data inconsistency. For example, it will be very helpful when you are uploading large amount of data using a mysqldump output.

警告：只有在进行单用户模式维护时才应该这样做。因为它可能会导致数据不一致。例如，当您使用 mysqldump 输出上传大量数据时，这将非常有用。

回答by AntonioCS

I normally only disable foreign key constraints when I want to truncate a table, and since I keep coming back to this answer this is for future me:

当我想截断一个表时，我通常只禁用外键约束，因为我一直回到这个答案，这是为将来的我准备的：

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE table;
SET FOREIGN_KEY_CHECKS=1;

回答by dnagirl

Instead of disabling your constraint, permanently modify it to ON DELETE SET NULL. That will accomplish a similar thing and you wouldn't have to turn key checking on and off. Like so:

不要禁用您的约束，而是将其永久修改为 ON DELETE SET NULL。这将完成类似的事情，您不必打开和关闭密钥检查。像这样：

ALTER TABLE tablename1 DROP FOREIGN KEY fk_name1; //get rid of current constraints
ALTER TABLE tablename2 DROP FOREIGN KEY fk_name2;

ALTER TABLE tablename1 
  ADD FOREIGN KEY (table2_id) 
        REFERENCES table2(id)
        ON DELETE SET NULL  //add back constraint

ALTER TABLE tablename2 
  ADD FOREIGN KEY (table1_id) 
        REFERENCES table1(id)
        ON DELETE SET NULL //add back other constraint

Have a read of this (http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) and this (http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html).

阅读一下这个（http://dev.mysql.com/doc/refman/5.5/en/alter-table.html）和这个（http://dev.mysql.com/doc/refman/5.5/en /create-table-foreign-keys.html）。

回答by Umar Tariq

To turn off foreign key constraint globally:

要全局关闭外键约束：

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

and for active foreign key constraint

和活动外键约束

SET GLOBAL FOREIGN_KEY_CHECKS = 1;

回答by svin

A very simple solution with phpmyadmin :

一个非常简单的 phpmyadmin 解决方案：

• In your table, go to SQLtab
• After you edit the SQL command that you want to run, there is a check box next to GO, named 'Enable foreign key checks'.
• Uncheck this check box and run your SQL. It will be automatically re-checked after executing.

• 在您的表格中，转到SQL选项卡
• 编辑要运行的 SQL 命令后，旁边有一个复选框GO，名为“启用外键检查”。
• 取消选中此复选框并运行您的 SQL。执行后会自动重新检查。

回答by RotS

For me just SET FOREIGN_KEY_CHECKS=0;wasn't enough. I was still having a com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException.

对我来说只是SET FOREIGN_KEY_CHECKS=0;不够。我仍然有一个com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException.

I had to add ALTER TABLE myTable DISABLE KEYS;.

我不得不添加ALTER TABLE myTable DISABLE KEYS;.

So:

所以：

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE myTable DISABLE KEYS;
DELETE FROM myTable;
ALTER TABLE myTable ENABLE KEYS;
SET FOREIGN_KEY_CHECKS=1;

回答by Chanoch

If the key field is nullable, then you can also set the value to null before attempting to delete it:

如果键字段可以为空，那么您还可以在尝试删除它之前将该值设置为空：

cursor.execute("UPDATE myapp_item SET myapp_style_id = NULL WHERE n = %s", n)
transaction.commit_unless_managed() 

cursor.execute("UPDATE myapp_style SET myapp_item_id = NULL WHERE n = %s", n)
transaction.commit_unless_managed()

cursor.execute("DELETE FROM myapp_item WHERE n = %s", n)
transaction.commit_unless_managed()

cursor.execute("DELETE FROM myapp_style WHERE n = %s", n)
transaction.commit_unless_managed()

回答by Julian

In phpMyAdmin you can select multiple rows then click the delete action. You'll enter a screen which lists the delete queries, you can uncheck the Foreign key check, and click on Yes to execute them.

在 phpMyAdmin 中，您可以选择多行，然后单击删除操作。您将进入一个列出删除查询的屏幕，您可以取消选中外键检查，然后单击是执行它们。

This will enable you to delete rows even if there is a ON DELETE restriction constrain.

这将使您能够删除行，即使存在 ON DELETE 限制约束。

回答by Valencia Starr

It's not a good idea to set a foreign key constraint to 0, because if you do, your database would not ensure it is not violating referential integrity. This could lead to inaccurate, misleading, or incomplete data.

将外键约束设置为 0 不是一个好主意，因为如果这样做，您的数据库将无法确保它不违反参照完整性。这可能会导致数据不准确、误导或不完整。

You make a foreign key for a reason: because all the values in the child column shall be the same as a value in the parent column. If there are no foreign key constraints, a child row can have a value that is not in the parent row, which would lead to inaccurate data.

您创建外键是有原因的：因为子列中的所有值都应与父列中的值相同。如果没有外键约束，子行的值可能不在父行中，这会导致数据不准确。

For instance, let's say you have a website for students to login and every student must register for an account as a user. You have one table for user ids, with user id as a primary key; and another table for student accounts, with student id as a column. Since every student must have a user id, it would make sense to make the student id from the student accounts table a foreign key that references the primary key user id in the user ids table. If there are no foreign key checks, a student could end up having a student id and no user id, which means a student can get an account without being a user, which is wrong.

例如，假设您有一个供学生登录的网站，每个学生都必须注册一个帐户作为用户。您有一张用户 ID 表，以用户 ID 作为主键；和另一个学生帐户表，以学生 ID 作为列。由于每个学生都必须有一个用户 ID，因此将学生帐户表中的学生 ID 设为引用用户 ID 表中主键用户 ID 的外键是有意义的。如果没有外键检查，学生可能最终拥有学生 ID 而没有用户 ID，这意味着学生可以在不是用户的情况下获得帐户，这是错误的。

Imagine if it happens to a large amount of data. That's why you need the foreign key check.

想象一下，如果它发生在大量数据上。这就是您需要外键检查的原因。

It's best to figure out what is causing the error. Most likely, you are trying to delete from a parent row without deleting from a child row. Try deleting from the child row before deleting from the parent row.

最好找出导致错误的原因。最有可能的是，您试图从父行中删除而不从子行中删除。在从父行删除之前尝试从子行删除。