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

Posted October 3, 2008 * Comments(3)

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;

Post to Twitter Tweet This

3 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 :D and now they say it is team work! peh

  3. gx on July 5th, 2010

    thank you, nice post :)

Leave a reply