MySQL: how to ignore checking of foreign key constraints for InnoDB tables
There is a session variable FOREIGN_KEY_CHECKS which allows to ignore checking of foreign key constraints for InnoDB tables.
If set to 1 (the default), foreign constraints are checked. If set to 0, they are ignored.
Setting FOREIGN_KEY_CHECKS to 0 also affects data definition statements like DROP TABLE which drops tables that have foreign keys that are referred to by other tables.
Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.
Using session variables:
SET FOREIGN_KEY_CHECKS = 0; SELECT @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 1; |
Comments(13)
Thank you, I was just looking for this.
Thank you my collegues were looking for this for 2 hours
and now they say it is team work! peh
thank you, nice post
Thanks this helped out.
Thanks, this helped a lit (again).
Thanks much for this, don’t know why I didn’t look for a solution like this sooner.
Can it also be used in case you want to delete records with check
Viktor,
Yes, it is possible to do something like this:
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM `t`;
SET FOREIGN_KEY_CHECKS = 1;
Also depending on your application needs you may want to consider setting foreign_key_checks = 0 permanently in the MySQL configuration file.
Thanks Alex! This is what I wanted.
Thanks, great help.
Thanks a lot from Mexico City
Can I use this, while importing into a child table?
Yes, there are no restrictions from the technical point of view.