According to what I read somewhere, the
… 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_CHECKSwith a value of
FOREIGN_KEY_CHECKSsomething 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?
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.