Disabling Foreign Key and Unique Checks in MySQL

30 06 2011

MySQL has a couple of Server System Variables which control your ability to violate constraints in DML or DDL for InnoDB tables. You can alter these variables dynamically by using the SET statement like so:

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;

Where 0 means “turn these checks off”. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships. Be warned that setting variables back to 1 does not trigger a scan of the tables data, anything inserted while it was turned off will not be checked for consistency.