How can I temporarily disable a foreign key constraint in MySQL

Question

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  quot DELETE FROM myapp item WHERE n    s quot   n  transaction commit unless managed     a foreign key constraint fails here  cursor execute  quot DELETE FROM myapp style WHERE n    s quot   n  transaction commit unless managed    Is it possible to temporarily disable constraints and delete anyway

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   Please uncheck the  quot Enable foreign key checks quot  checkbox  and click on Yes to execute them  This will enable you to delete rows even if there is an ON DELETE restriction constraint

User · Answer

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

User · Answer

Try DISABLE KEYS or SET FOREIGN KEY CHECKS 0   Make sure to SET FOREIGN KEY CHECKS 1   after

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to django

Examples related to django-models