Magento 2 Delete All Categories from Database

Question

I want to delete all categories from the database so I can import new ones. I need to also delete the rewrites. I tried this code:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE r5t_catalog_category_entity;

TRUNCATE TABLE r5t_catalog_category_entity_datetime;

TRUNCATE TABLE r5t_catalog_category_entity_decimal; TRUNCATE TABLE r5t_catalog_category_entity_int; TRUNCATE TABLE r5t_catalog_category_entity_text; TRUNCATE TABLE r5t_catalog_category_entity_varchar; TRUNCATE TABLE r5t_catalog_category_product; TRUNCATE TABLE r5t_catalog_category_product_index;

INSERT INTO r5t_catalog_category_entity (entity_id, attribute_set_id, parent_id, created_at, updated_at, path, position, level, children_count) VALUES ('1', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'), ('2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');

INSERT INTO r5t_catalog_category_entity_int (value_id, attribute_id, store_id, entity_id, value) VALUES ('1', '69', '0', '1', '1'), ('2', '46', '0', '2', '1'), ('3', '69', '0', '2', '1');

INSERT INTO r5t_catalog_category_entity_varchar (value_id, attribute_id, store_id, entity_id, value) VALUES ('1', '45', '0', '1', 'Root Catalog'), ('2', '45', '0', '2', 'Default Category');

DELETE FROM r5t_url_rewrite WHERE entity_type = 'category';

SET FOREIGN_KEY_CHECKS = 1;

And I get an error:

1701 - Cannot truncate a table referenced in a foreign key constraint (`xxxxxx_maged`.`r5t_catalog_category_entity_decimal`, CONSTRAINT `R5T_CAT_CTGR_ENTT_DEC_ENTT_ID_R5T_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `matt6vr_maged`.`r5t_catalog_ca)

When I uncheck ‘Enable Foreign Key Checks’ and the tables deleted but I then couldn’t import the categories.

Thanks! Stan

UPDATE: I ran the commands from the mysql prompt and didn't get any errors. But now when I try to import categories I get this in the admin:

ROW: 67 ERROR: Unique constraint violation found

Thought's?

Answer 1

There are two ways

First One Is :

DELETE FROM catalog_category_entity WHERE entity_id > 2;
DELETE FROM url_rewrite WHERE entity_type = 'category';


Second Is :

TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

INSERT INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`position`,`level`,`children_count`) values (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);
INSERT INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) values (1,3,32,0,2,1),(2,3,32,1,2,1);
INSERT INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) values (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');