Sometimes, you face database issues like this ‘integrity constraint violation: 1062 Duplicate entry ‘100000001’ for key’ . if you are not familiar with Magento, may be you will feel blind (like i was), debugging won’t go smoothly as usual.
After studying Magento deeper to its core, i found that Magento numbering system depends on a table called ‘eav_entity_store’. this table stores increment ids of many entities from Magento.
Back to our integrity constrant violation issues, we often have such troubles after upgrading Magneto to newer version. Mostly because of some increment ids are outdate. what we need to do to fix it is to update it to right number. for example, i my case, i have integrity constraint issue with creditmemo. i found that creditmemo have entity_type_id = 7 so i need to update increment_last_id from eav_entity_store table to what i found in sale_flat_creditmemo. This is my SQL script
DELETE FROM magento_eav_entity_store WHERE entity_type_id = 7; INSERT INTO `magento_eav_entity_store` (entity_type_id,store_id,increment_prefix,increment_last_id) SELECT '7' AS entity_type_id, store_id AS store_id, store_id AS increment_prefix, increment_id AS increment_last_id FROM (SELECT * FROM magento_sales_flat_creditmemo ORDER BY increment_id DESC) AS temp GROUP BY store_id;
note : i have magento_ as table prefix, you can modify it to fit your case
As you can see, i deleted all record that have entity_type_id = 7 (creditmemo entity id), then i created new row with data from sale_flat_creditmemo table. And that’s all 😀
i hope you find this post helpful for your trouble.
