1

I am trying to truncate some mySQL tables from phpMyAdmin web interface, but for foreign keys constraint I am not able to do it. Is there a way to SET FOREIGN_KEY_CHECKS = 0 in phpMyAdmin?

0

You must drop any foreign key constraints first to drop table. For example, if table name "customer_group" and foreign key constraint entity_id. Here you need to remove first "entity_id" then customer_group.

2
0

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;

Again, remember to enable if thereafter:

ALTER TABLE table_name ENABLE KEYS;

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_name1 DROP FOREIGN KEY fk_name1; 
ALTER TABLE table_name2 DROP FOREIGN KEY fk_name2;

Hope this help you

Thanks ...

2
  • Thanks, I will need to truncate many tables like the link here: magento.stackexchange.com/questions/3701/…. But in phpMyAdmin SET FOREIGN_KEY_CHECKS=0 does not work. So is there a easy way to do it? – hln Jul 1 '20 at 13:52
  • This is best way first disable table FOREIGN_KEY and after truncate and after enable. Thanks... – Mohit Patel Jul 1 '20 at 14:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.