Question:
According to what I read somewhere, the FOREIGN_KEY_CHECKS
:
… specifies whether or not to check foreign key constraints for InnoDB tables.
That is, if the guy wants to disable the checking of foreign keys, he does:
SET FOREIGN_KEY_CHECKS = 0;
I know it's common to use this command in case of dumps to do database migration, backups and things like that… But I keep wondering if something that can disable foreign key checking can be harmful to an application or not.
I have the following questions:
- In what types of scenarios would it be valid or not to use
FOREIGN_KEY_CHECKS
with a value of0
? -
FOREIGN_KEY_CHECKS
something to consider in restricting in production environment? Is it something that can cause some kind of inconsistency in my application/database? - If it is possible to disable the modification in
FOREIGN_KEY_CHECKS
, what is the procedure?
Answer:
I wouldn't say it would bring problems to your database in 100% of the cases, assuring yourself that it won't harm you in the future. I use this functionality only to recover from backups, but never during the execution of a query by the Application, as disabling it can facilitate attacks or abnormalities in the database. But like everything else in the information, each case is a case, for example:
-
When recovering from very large backups, some servers have limited SQL execution time, keeping disabled may be faster.
-
In some cases, the database is out of date for you to maintain, with foreign keys between unused fields.
-
There are also some other specific cases, where the tables are populated in a random order, and instead of wasting time to sort them, some prefer to disable the check.