How to temporarily disable a foreign key constraint in MySQL?

Is it possible to temporarily disable constraints in MySQL?

I have two models, each with a ForeignKey to the other one. Deleting 

instances of a model returns an error because of the ForeignKey constraint:

Is it possible to temporarily disable constraints and delete anyway?

 Answers

Try DISABLE KEYS or

SET FOREIGN_KEY_CHECKS=0;

make sure to

SET FOREIGN_KEY_CHECKS=1;

after.

---

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;

---

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

delete it:

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.

---

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.

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