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("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?

Either I don't get what you want to do, or what you are trying to do is **very, very, very ugly**. Even if you can do it, you probably shouldn't. — Dariusz, Mar 19 '13 at 14:06
Dropping and reapplying an FK _is_ changing your db. You're trying to defy the very constraints that allow the system to see some sense, it has no regard that an FK could be a temporary thing, and if it did know, it would panic. — Grant Thomas, Mar 19 '13 at 14:07
Its strange what you trying to do. But which database are you using? — andrefsp, Mar 19 '13 at 14:08
@andrefsp: I'm using MySQL. I'll edit my question to add that. — jul, Mar 19 '13 at 14:10
what if, instead of disabling your constraint, you permanently modified it to `ON DELETE SET NULL`? That would accomplish a similar thing and you wouldn't have to turn key checking on and off. — dnagirl, Mar 19 '13 at 14:15
You should do what @dnagirl its telling you to do. Its way more reasonable and its the way to ensure you don't mess in a really nasty way with your data. Playing with it like what you want to do might lead you to serious integrity and consistency problems. — andrefsp, Mar 19 '13 at 14:28
@Dariusz I do it all the time resetting certain tables in my test data db, without regenerating the whole thing. Not sure why it's do bad — Max, Dec 29 '21 at 11:23

score 1731 · Accepted Answer · edited Jun 27 '20 at 16:37

1731

Try DISABLE KEYS or

SET FOREIGN_KEY_CHECKS=0;

Make sure to

SET FOREIGN_KEY_CHECKS=1;

after.

edited Jun 27 '20 at 16:37

Peter Mortensen

30,738
21
105
131

answered Mar 19 '13 at 14:07

Andrew Campbell

17,665
2
16
25

18

is this something that is set for mysql as a whole or just that session? – tipu Oct 31 '13 at 21:41
35

I believe it is per session. – Andrew Campbell Nov 04 '13 at 15:02
13

http://serverfault.com/questions/291100/with-mysql-how-long-does-an-alter-table-disable-keys-statement-last#comment820481_291100 , Also note that you [**cannot** `disable keys`](http://serverfault.com/questions/234893/how-to-disable-keys-in-mysql-innodb/234901#comment820478_234901) for Innodb – Pacerier Feb 24 '15 at 04:19
2

Can I just disable FOREIGN_KEY_CHECKS for a single table? – wheeleruniverse Dec 20 '18 at 13:53
@Pacerier From reading that, it appears you can, but only for a single session. – Brett Feb 26 '19 at 21:37
`SET FOREIGN_KEY_CHECKS=0;` is not working for me. Is this working from newer version of mysql. – Musaddiq Khan Oct 17 '19 at 10:39
2

To clarify @Pacerier's comment: For Innodb, you cannot `DISABLE KEYS`. But you *can* accomplish what is requested in this question, using the other command shown: `SET FOREIGN_KEY_CHECKS=0;` (I thought at first he was saying there was no way to accomplish this.) – ToolmakerSteve Oct 24 '19 at 13:05
@MusaddiqKhan - Yes: [MySQL 8 foreign_key_checks](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks). As mentioned in comments above, this is only for the current session/connection. If you still can't get it to work, *post a new question*, with good detail about what you tried, and what happened - error message? – ToolmakerSteve Oct 24 '19 at 13:09
A word of caution: MySQL doesn't revalidate the foreign keys when you reactivate the checks. This can cause serious referential errors. – jor Feb 11 '20 at 16:07
Wish I could upvote this answer, everytime I came back here. – MRodrigues Nov 04 '22 at 10:59

berniey · Answer 2 · 2014-09-26T19:20:55.683

187

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.

edited Sep 26 '14 at 19:20

answered Jul 07 '14 at 02:05

berniey

2,772
1
18
8

2

this is what I needed to know, so its not great practice, but this guys answer should be scoring higher... – ftrotter Dec 12 '14 at 22:53
1

This worked for me after trying the 'best answer' didn't work for me. Perhaps an explanation of the difference could be added. – hexnet Aug 27 '15 at 12:37
9

@hexnet The difference is that just `SET FOREIGN_KEY_CHECKS` just changes the value for the _current connection_, while `SET GLOBAL ..` changes the value for _all connections_, including future connections. If you just do `SET FOREIGN..` in one window, then try to apply the statement in a different window (over a different connection), the value has not changed there. With `GLOBAL`, the same variable has the same value for both connections. – MatsLindh Apr 26 '16 at 14:03
The only thing that could help me when playing back a larger dump (6+ GB) <3 – Max Sep 15 '16 at 15:11
This doesn't work for me. When I try, I see:`ERROR 1228 (HY000): Variable 'foreign_key_checks' is a SESSION variable and can't be used with SET GLOBAL` – Mike B Dec 09 '16 at 00:25
Try UPPERCASE @MikeB – berniey Dec 09 '16 at 02:53
@berniey no luck. It's alright though. I used `alter` statements to temporarily disable the foreign key constraints. – Mike B Dec 09 '16 at 19:45
@MikeB, I think it has to do with the tool that you use to issue the command (it works with `myqsl` command line tool). I've been using the command on v5.5 and 5.6 and it works fine. Document suggested ut even works on v5.7. http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html. Also, case doesn't matter (pardon on my earlier suggestion) – berniey Dec 11 '16 at 21:15
Would like to note that if you connect to MySQL that has foreign key checks enabled prior to issuing the global query, that the current session state for foreign key checks is preserved. i.e. `SET GLOBAL FOREIGN_KEY_CHECKS=0; SELECT @@FOREIGN_KEY_CHECKS;` will return `1` until you reconnect to start a new session – Will B. Oct 20 '18 at 00:30
this doesn't work for me (mysql 5.7). However the accepted answer "SET FOREIGN_KEY_CHECKS=0;" does work. – Blisterpeanuts Oct 15 '20 at 18:42

score 72 · Answer 3 · answered Mar 25 '15 at 16:48

72

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;

answered Mar 25 '15 at 16:48

AntonioCS

8,335
18
63
92

it works like a charm! thanks! – kollein Sep 02 '23 at 09:31

score 28 · Answer 4 · answered Mar 19 '13 at 14:33

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

Beware alterting table can take a long time, better to set the server global for `FOREIGN_KEY_CHECKS` to 0 and put it back once the dirty work is done. Besides it might lock for writing your tables. — Aki, Jan 13 '14 at 14:57
Won't that break the reference when altering the remote column type? (It seems my client renames a modified temp table to the original table name.) — Cees Timmerman, Mar 03 '16 at 10:37

score 28 · Answer 5 · edited Jun 27 '20 at 16:41

28

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;

edited Jun 27 '20 at 16:41

Peter Mortensen

30,738
21
105
131

answered Jan 11 '18 at 09:49

Umar Tariq

1,191
1
13
14

1

This appears to be an exact duplicate of an [existing, older answer from @berniey](https://stackoverflow.com/a/24602164) – ᴍᴇʜᴏᴠ Dec 15 '22 at 22:28

score 13 · Answer 6 · edited Jun 27 '20 at 16:41

13

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.

edited Jun 27 '20 at 16:41

Peter Mortensen

30,738
21
105
131

answered Apr 19 '17 at 04:26

svin

149
1
3

3

Thanks! Indeed solution `SET FOREIGN_KEY_CHECKS=0; ..... SET FOREIGN_KEY_CHECKS=1;` didn't work for me in PHPMyAdmin because I forgot to uncheck the 'Enable foreign key checks' checkbox. In PHPMyAdmin you can skip these SET commands and just uncheck the checkbox. – Jan Jan 07 '18 at 10:31

score 12 · Answer 7 · answered Oct 12 '18 at 10:16

12

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;

answered Oct 12 '18 at 10:16

RotS

2,142
2
24
30

1

FYI, mySQL 5.7 throws warning, InnoDB engine doesn't have this option when running DISABLE KEYS command. – wheeleruniverse Dec 20 '18 at 13:56
this did work, without the alter table it also didn't work for me – David Kabii Jul 03 '19 at 11:43

Julian · Answer 8 · 2020-06-30T09:22:04.160

5

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

edited Jun 30 '20 at 09:22

answered Dec 31 '15 at 10:57

Julian

4,396
5
39
51

score 3 · Answer 9 · answered Apr 30 '14 at 21:18

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

score -1 · Answer 10 · edited Nov 11 '16 at 09:24

-1

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.

edited Nov 11 '16 at 09:24

Pang

9,564
146
81
122

answered Jan 14 '15 at 10:21

Valencia Starr

99
3

2

True, there's always a trade-off. – Pacerier Feb 23 '15 at 15:44
29

No one is saying to run it like this forever. You turn off constraints, bulk load some data, and flip it back on. No big deal, people do it all the time. – bwawok Nov 17 '15 at 17:39
it is necessary for bulk imports, for performance at least, it is very common. also sometimes you just need the data to be restored, then you can make your checks. – Firas Abd Alrahman Feb 14 '17 at 23:43
3

This is not an answer to the question. – Koray Tugay Sep 05 '17 at 10:10
Note, his question is how to do this temporarily. This is required when doing certain maintenance and data imports. The caveat of course is that your import scripts become responsible for data integrity. Then, later when the indexes and constraints are turned back on, the db will tell you if something is broken. – mcstar Nov 13 '17 at 20:38
have you ever used phpMyAdmin? – Santi Jun 04 '18 at 12:17
There are situations where this is useful. – Serinus Oct 22 '19 at 03:21
Example: I have a table with a self-referential foreign key. The two fields are main_character_id and character_id. Each character_id must have at least one matching main_character_id. I generally want the foreign key constraint here, but it prevents me from deleting any main character (which would have the same value in both fields). I could solve this by adding a main_character_id of 0 or -1 in the table that's used to allow update and deletion of other main characters, but is having false data better? Probably not. – Serinus Oct 22 '19 at 03:27
I can not agree with you as it's not a permanent solution. I know this solution helped me on several occasions. Importing tables one by one in the order of constraints does not seem to be practical. – Duke Dec 06 '22 at 10:54

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

10 Answers10

Linked

Related