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?

hln
  • 125
  • 1
  • 7

2 Answers2

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.

softinfoline
  • 104
  • 8
  • Thanks, do you have a full query for it? – hln Jul 01 '20 at 12:11
  • @hln You may find this URL helpful. https://www.mysqltutorial.org/mysql-foreign-key/#:~:text=Drop%20MySQL%20foreign%20key%20constraints,after%20the%20ALTER%20TABLE%20keywords. – Wilson Hauck Jul 13 '20 at 21:38
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 ...

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