IQCode Blog > Components > MySQL – How to temporarily disable a foreign key constraint?
Cute Astronaut Riding Rocket And Waving Hand Cartoon Vector Icon Illustration. Science Technology Icon Concept Isolated Premium Vector. Flat Cartoon Style
Categories

MySQL – How to temporarily disable a foreign key constraint?

  • Posted byby IQCode
  • Updated

To disable foreign key constraints when you want to truncate a table:

Use FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS=0;

and remember to enable it when you’re done:

SET FOREIGN_KEY_CHECKS=1;

Or you can use DISABLE KEYS:

ALTER TABLE table_name DISABLE KEYS;

Do not forget to enable if thereafter:

ALTER TABLE table_name ENABLE KEYS;

Note that DISABLE KEYS does not work for InnoDB tables and is not supported by MyISAM.

Use ON DELETE SET NULL

If you don’t want to turn key checking on and off, you can permanently modify it to ON DELETE SET NULL:

Delete the current foreign key first:

ALTER TABLE table_name_1 DROP FOREIGN KEY fk_name_1; 
ALTER TABLE table_name_2 DROP FOREIGN KEY fk_name_2;

Then add the foreign key constraints back

 ALTER TABLE table_name_1  
 ADD FOREIGN KEY (table_2_id)  
REFERENCES table_2(id)         ON DELETE SET NULL;  
 
 ALTER TABLE table_name_2   
 ADD FOREIGN KEY (table_1_id)        
  REFERENCES table_1(id)         ON DELETE SET NULL;

Post navigation