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.