Menu
Foreign Key Checks. In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the foreign_key_checks variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity.
Foreign Key Check in MySQL can prevent you from making some database updates to tables that have foreign key constraints. In such cases, you can temporarily disable foreign key check in MySQL, make your updates and enable foreign key check in MySQL afterwards. Here's how to disable foreign key constraint in MySQL.
To see foreign key relationships of a table: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name';
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;
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
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.
# 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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With