Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I temporarily disable a foreign key constraint in MySQL?

Is it possible to temporarily disable constraints in MySQL?

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

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?

like image 498
jul Avatar asked Mar 19 '13 14:03

jul


People also ask

Can we disable foreign key constraint in MySQL?

You can disable foreign key check in MySQL by setting the system variable foreign_key_checks to 0. However, please note, after you enable foreign key checks, MySQL will not re-validate your existing data that you added after disabling foreign key check. It will only check any new additions/updates to your database.


2 Answers

Try DISABLE KEYS or

SET FOREIGN_KEY_CHECKS=0; 

Make sure to

SET FOREIGN_KEY_CHECKS=1; 

after.

like image 185
Andrew Campbell Avatar answered Sep 23 '22 13:09

Andrew Campbell


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.

like image 28
berniey Avatar answered Sep 22 '22 13:09

berniey