How to disable and enable all constraints in table mysql incl. PK FK CHK UNI etc

Question

I need to load test data to table and I want to disable every possible constraint, i ve found how to globally turn off the foreign keys, but I need to disable all constraints, PK,FK, CHK, UNIQ etc. Is it possible to do it globally? Of course after that I have to tun all constraints on.

Answer 1

Use the following to disable constraints:

-- disable UNIQ, PK, ...
ALTER TABLE <tablename> DISABLE KEYS;
-- diable FK
SET FOREIGN_KEY_CHECKS=0;

Check for instance this site for more examples. Restore with:

SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE <tablename> ENABLE KEYS;

From the reference:

• foreign_key_checks
• DISABLE KEYS: " Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. ..."

p.s.: from InnoDB performance tuning tips

SET autocommit=0; 
SET unique_checks=0; 
SET foreign_key_checks=0;