Does MySQL foreign_key_checks affect the entire database?

The Question :

204 people think this question is useful

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?

The Question Comments :
  • 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.

The Answer 1

254 people think this answer is useful

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;

The Answer 2

99 people think this answer is useful

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

The Answer 3

13 people think this answer is useful

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.

The Answer 4

10 people think this answer is useful
# 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.

The Answer 5

1 people think this answer is useful

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

The Answer 6

-2 people think this answer is useful

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

Tags:

Add a Comment