217

When I execute this command in MySQL:

SET FOREIGN_KEY_CHECKS=0;

Does it affect the whole engine or it is only my current transaction?

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
Sean Nguyen
  • 12,528
  • 22
  • 74
  • 113
  • 15
    test: log into mysql: show variables like '%FOREIGN%';SET FOREIGN_KEY_CHECKS=0; After that log into mysql using a different console. I can see that show variables like '%FOREIGN%' is ON instead of OFF. – Sean Nguyen Dec 27 '11 at 23:13

6 Answers6

269

It is session-based, when set the way you did in your question.

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

According to this, FOREIGN_KEY_CHECKS is "Both" for scope. This means it can be set for session:

SET FOREIGN_KEY_CHECKS=0;

or globally:

SET GLOBAL FOREIGN_KEY_CHECKS=0;
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Almo
  • 15,538
  • 13
  • 67
  • 95
100

Actually, there are two foreign_key_checks variables: a global variable and a local (per session) variable. Upon connection, the session variable is initialized to the value of the global variable.
The command SET foreign_key_checks modifies the session variable.
To modify the global variable, use SET GLOBAL foreign_key_checks or SET @@global.foreign_key_checks.

Consult the following manual sections:
http://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

Ron Inbar
  • 2,044
  • 1
  • 16
  • 26
  • 1
    Does setting the foreign_key_checks on each request costly? I've got a script to upgrade the DB and I wouldn't want anybody else to be able to override the foreign key checks by default during that upgrade. So I'd make millions of queries and I wondered if a SET would be significant or not? – Aki Jan 13 '14 at 15:01
  • @Aki If you are upgrading the DB, I'd say you are better off locking access for everyone else. At least for writing. Otherwise, you can expect all kinds of concurrent access issues. – tishma May 13 '15 at 09:26
  • 1
    Great answer and distinction. It's important to realize the consequences of how it works. It means that you can't set the GLOBAL `foreign_key_checks` and in that same session expect it to ignore foreign keys constraints. You need to set the non-global variable. – Tyler Collier Oct 10 '16 at 16:53
13

As explained by Ron, there are two variables, local and global. The local variable is always used, and is the same as global upon connection.

SET FOREIGN_KEY_CHECKS=0;
SET GLOBAL FOREIGN_KEY_CHECKS=0;

SHOW Variables WHERE Variable_name='foreign_key_checks'; # always shows local variable

When setting the GLOBAL variable, the local one isn't changed for any existing connections. You need to reconnect or set the local variable too.

Perhaps unintuitive, MYSQL does not enforce foreign keys when FOREIGN_KEY_CHECKS are re-enabled. This makes it possible to create an inconsistent database even though foreign keys and checks are on.

If you want your foreign keys to be completely consistent, you need to add the keys while checking is on.

Bouke Versteegh
  • 4,097
  • 1
  • 39
  • 35
  • 1
    Can you elaborate on...."If you want your foreign keys to be completely consistent, you need to add the keys while checking is on." – user2782001 Mar 30 '16 at 18:20
  • 4
    Let's say you have a table with referencing id's, but some referenced records are missing. If you add the foreign key (FK) while FOREIGN_KEY_CHECKS are ON, then Mysql will raise an error and refuse to add the FK, because of the broken reference. When you add the foreign key while FOREIGN_KEY_CHECKS are OFF, mysql continues without error. Even when you enable the checks afterwards, there will be no error. You now have a table with inconsistent data, even though there is a FK. As such, the existence of a FK is no guarantee of database consistency, unless it was added while FK checks were on. – Bouke Versteegh Apr 01 '16 at 07:20
10
# will get you the current local (session based) state.
SHOW Variables WHERE Variable_name='foreign_key_checks';

If you didn't SET GLOBAL, only your session was affected.

Almo
  • 15,538
  • 13
  • 67
  • 95
Mike Karras
  • 121
  • 1
  • 5
0

I had the same error when I tried to migrate Drupal database to a new local apache server(I am using XAMPP on Windows machine). Actually I don't know the meaning of this error, but after trying steps below, I imported the database without errors. Hope this could help:

Changing php.ini at C:\xampp\php\php.ini

max_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M

Changing my.ini at C:\xampp\mysql\bin\my.ini

max_allowed_packet = 1024M
Saeed cr7
  • 9
  • 2
-3

In case of using Mysql query browser, SET FOREIGN_KEY_CHECKS=0; does not have any impact in version 1.1.20. However, it works fine on Mysql query browser 1.2.17

user2682955
  • 21
  • 6