Magento 2 - Integrity constraint violation: 1062 Duplicate entry

Upon trying to run bin/magento setup:upgrade on a new instance of an existing Magento 2 project I recently ran into the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '27698' for key 'catalog_url_rewrite_product_category.PRIMARY', query was: ALTER TABLE `catalog_url_rewrite_product_category` ADD CONSTRAINT PRIMARY KEY (`url_rewrite_id`)

As the error message suggests, the reason for this error is that you have a duplicate entry within a database table. These may be caused by entirely duplicated rows or they may just share the same primary key.

To resolve this you can manually work through the database table and remove the duplicate rows or you can run the following SQL script:

DELETE t1.* FROM catalog_url_rewrite_product_category t1 INNER JOIN (SELECT url_rewrite_id FROM catalog_url_rewrite_product_category GROUP BY url_rewrite_id HAVING COUNT(*)>1) t2 ON t1.url_rewrite_id = t2.url_rewrite_id

Be sure to replace all occurrences of "catalog_url_rewrite_product_category" and "url_rewrite_id" with the correct table and column name respectively.

To consult with us on other Magento web development issues like the above, Adobe commerce or have a Magento audit, contact us today.