Does MySQL foreign key checks affect the entire database

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Does MySQL foreign_key_checks affect the entire database?

Examples related to mysql