8

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.

R. Nec
  • 315
  • 2
  • 5
  • 10

1 Answers1

5

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:

p.s.: from InnoDB performance tuning tips

SET autocommit=0; 
SET unique_checks=0; 
SET foreign_key_checks=0;
Trinimon
  • 13,839
  • 9
  • 44
  • 60
  • 1
    Is it possible to disable all keys globally in schema? – R. Nec Mar 20 '15 at 12:31
  • Disabling is session based as per this post: http://stackoverflow.com/questions/8538636/does-mysql-foreign-key-checks-affect-the-entire-database – Trinimon Mar 20 '15 at 12:33
  • I issued disable keys command. My schema uses innoDB and returned me warning "Table storage engine for table' doesn't have this option" – R. Nec Mar 20 '15 at 12:39
  • Ok, I see. May be this helps you out: http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html – Trinimon Mar 20 '15 at 12:48