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:


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';


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



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');
  • Hi, I had found something similar and tried it and it caused other problems. I ended up doing something different so I cannot confirm that the above will work. If I run into trouble again I will be sure to try it. Thanks! – stanhook Dec 26 '19 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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