Unique constraint violation found when adding subcategory

[akshaykamba454]

While adding subcategory getting error - Unique constraint violation found 

In Error log found this -

[2020-06-19 13:28:54] main.CRITICAL: Unique constraint violation found {"exception":"[object] (Magento\\Framework\\Exception\\AlreadyExists Exception(code: 0): Unique constraint violation found at /srv/public_html/vendor/magento/framework/EntityManager/Operation/Update.php:121, Magento\\Framework\\DB\\Adapter\\DuplicateException(code: 1062): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '433-7281' for key 'CATALOG_CATEGORY_PRODUCT_CATEGORY_ID_PRODUCT_ID', query was: INSERT INTO `catalog_category_product` (`category_id`,`product_id`,`position`) VALUES (?, ?, ?), (?, ?, ?) at /srv/public_html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:585, Zend_Db_Statement_Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '433-7281' for key 'CATALOG_CATEGORY_PRODUCT_CATEGORY_ID_PRODUCT_ID', query was: INSERT INTO `catalog_category_product` (`category_id`,`product_id`,`position`) VALUES (?, ?, ?), (?, ?, ?) at /srv/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '433-7281' for key 'CATALOG_CATEGORY_PRODUCT_CATEGORY_ID_PRODUCT_ID' at /srv/public_html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)"} []

[Bhanu Periwal · Accepted solution]

Hi @akshaykamba454 

Please check with your custom module, is there any observer event you are using which can trigger after save subcategory like :

"catalog_product save_commit_after"

 which may have some bug and generate error. To fix this, you can removed the bug from my custom module.

Another way to resolve, open the "catalog_product_entity" table in phpmyadmin and remove the record with the key mentioned in the exception error . 

It may help you to resolve issue.

If issue resolve, please click on 'Kudos' & Accept as Solution!

[akshaykamba454]

Thanks for your reply. 

I tried by deleting entries from table catalog_product_entity but still getting an error while saving category.

[phagento]

This can sometimes happen when a database gets corrupt, often from 3rd party desktop type software or a badly written extension.

To fix this, first check that this is indeed the issue. Backup your database and then run the following SQL to see if any product and/or category associations are incorrect:

# Determine incorrect product associations
SELECT * FROM `catalog_category_product` WHERE
product_id not in (select entity_id from catalog_product_entity);

# Determine incorrect categories
SELECT * FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);

If that returns a record, you'll know exactly which product is in a non-existent category or which category has a non-existent product. Run the following SQL to then remove those invalid records:

# Delete incorrect product associations
DELETE FROM `catalog_category_product` WHERE 
product_id not in (select entity_id from catalog_product_entity);

# Delete incorrect categories
DELETE FROM `catalog_category_product` WHERE 
category_id not in (select entity_id from catalog_category_entity);