MySQL: how to ignore checking of foreign key constraints for InnoDB tables

Posted October 3, 2008 * Comments(21)

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;

21 Comments so far

  1. Joao on October 19th, 2009

    Thank you, I was just looking for this.

  2. Abc on February 17th, 2010

    Thank you my collegues were looking for this for 2 hours 😀 and now they say it is team work! peh

  3. gx on July 5th, 2010

    thank you, nice post 🙂

  4. Darren L on October 5th, 2010

    Thanks this helped out.

  5. Didier on December 12th, 2010

    Thanks, this helped a lit (again).

  6. Jstall on March 30th, 2011

    Thanks much for this, don’t know why I didn’t look for a solution like this sooner.

  7. Viktor on August 13th, 2011

    Can it also be used in case you want to delete records with check

  8. Alex on August 15th, 2011

    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.

  9. Naveen on September 21st, 2011

    Thanks Alex! This is what I wanted.

  10. Erik on June 26th, 2012

    Thanks, great help.

  11. Jaime de la Torre on December 28th, 2012

    Thanks a lot from Mexico City

  12. senthilvel on March 22nd, 2013

    Can I use this, while importing into a child table?

  13. Alex on March 29th, 2013

    Yes, there are no restrictions from the technical point of view.

  14. ram on October 8th, 2013

    “SET FOREIGN_KEY_CHECKS = 0;” is disabling the foreign key constraints but “SET FOREIGN_KEY_CHECKS = 1;” isn’t enabling the constraint successfully.

    Can you suggest why?

  15. Alex on December 2nd, 2013

    Ram,

    I suppose one reason could be related to data integrity, e.g. inconsistent foreign keys across tables. What does the error message say?

  16. San on February 25th, 2014

    Hi,

    Is there a way that we can set this option in .ini file to make it permanent during a restore operation and remove it later?

  17. Alex on April 8th, 2014

    Hi San,

    It does not seem this option is supported within config file: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html.

  18. Lois on January 11th, 2015

    Wow! This has helped out alot…! I mean like seriously I’ve searched 4 dis a long time, nd finally…! Yay!

  19. Wes on February 20th, 2015

    Thanks!

  20. Cammi on November 23rd, 2016

    Wonderful. Thanks!

  21. Andreas on February 18th, 2017

    Why would you set it in your ini file?
    Its only really needed for maintenance.

    If never want MySQL to check foreign keys, then simply remove the constrain. Everything else is just bad practice

Leave a reply