CODEWITHSUNDEEP
pythonsqlmysqldjangodjango-models
jul
jul

Reputation: 37464

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?

Upvotes: 854

Views: 977619

Answers (9)

Julian
Julian

Reputation: 4666

In phpMyAdmin you can select multiple rows and can then click the delete action. You'll enter a screen which lists the delete queries. It looks like this:

enter image description here

Please uncheck the "Enable foreign key checks" checkbox, and click on Yes to execute them.

This will enable you to delete rows even if there is an ON DELETE restriction constraint.

Upvotes: 7

Umar Tariq
Umar Tariq

Reputation: 1297

To turn off the foreign key constraint globally:

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

And for the active foreign key constraint:

SET GLOBAL FOREIGN_KEY_CHECKS = 1;

Upvotes: 32

svin
svin

Reputation: 149

A very simple solution with phpMyAdmin:

  • In your table, go to the SQL tab
  • 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 rechecked after executing.

Upvotes: 13

Andrew Campbell
Andrew Campbell

Reputation: 18645

Try DISABLE KEYS or

SET FOREIGN_KEY_CHECKS=0;

Make sure to

SET FOREIGN_KEY_CHECKS=1;

after.

Upvotes: 1828

RotS
RotS

Reputation: 2410

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

I had to add 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;

Upvotes: 13

AntonioCS
AntonioCS

Reputation: 8496

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;

Upvotes: 80

berniey
berniey

Reputation: 2932

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.

Upvotes: 199

Chanoch
Chanoch

Reputation: 593

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()

Upvotes: 3

dnagirl
dnagirl

Reputation: 20456

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:

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).

Upvotes: 29

Related Questions