mysql – Is there any risk in using "SET FOREIGN_KEY_CHECKS = 0"?

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 of 0 ?
  • 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.

Post Views: 7
Scroll to Top