Magento – Fix SQLSTATE[23000] Integrity Constraint Violation: Duplicate Entry

databasemagento-1.7onepage-checkoutpayment-gateway

I am using "Sage Pay" for payment transaction in magento. But when I place order it show the following error:

An sage pay error ocurred: SQLSTATE[23000]: Integrity constraint violation:1062 Duplicate entry '2000734" for key 'UNQ_SALES_FLAT_INVOICE_INCREMENT_ID'

Best Answer

To fix this issue, you need to change your increment_last_id of your invoice entity type.

By default in magento invoice entity type is 6. Which you can find in eav_entity_type table.

You need to change increment_last_id value for your store. The increment_last_id is in eav_entity_store table. You can run following query to update your invoice increment_last_id. Where X is your new increment_last_id and STORE_ID_HERE is your store id to whom you changing invoice increment_last_id.

UPDATE eav_entity_store
       INNER JOIN eav_entity_type
         ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
SET    eav_entity_store.increment_last_id = 'X'
WHERE  eav_entity_type.entity_type_code = 'invoice'
       AND eav_entity_store.store_id = {STORE_ID_HERE};

UPDATE `eav_entity_store` SET `increment_last_id` = 'X' WHERE `entity_type_id` = '6';

Notice that the value for increment_last_id already contains the increment_prefix. Use your entity_type increment_prefix

For more details check here : https://www.warpconduit.net/2012/04/18/how-to-change-the-order-increment-id-and-prefix-in-magento/

Hope it helps.

Related Solutions

Magento 2.3 Database – Fix Integrity Constraint Violation for Duplicate Entry

The error occurs if there are orders in the Magento 1 database with no increment_id set. You can check if you have such orders by running the following SQL statement in your Magento 1 database:

select count(*) from sales_flat_order where increment_id = '' OR increment_id is NULL;

If the returned value is greater than 0, you should update the tables sales_flat_order and sales_flat_order_grid in such a way to ensure that all your orders have increment_ids and those are unique.

If you find incrment_ids in the sales_flat_order_grid table for all orders, just take the values from there with the following statemen:

update sales_flat_order o 
join sales_flat_order_grid g 
    on o.entity_id = g.entity_id  
set o.increment_id = g.increment_id 
where o.increment_id = '' OR o.increment_id is NULL;

If you don't find orders without any increment_id it might be the best option to create arbitrary increment_ids. For example you can use a SQL update statement which builds an increment_id based on the entity_id and a prefix of your choice (just replace the prefix in the sample statements with something which doesn't conflict with existing increment_ids):

update sales_flat_order set increment_id = concat ('990000', entity_id) where increment_id is null;
update sales_flat_order_grid set increment_id = concat ('990000', entity_id) where increment_id is null;

Magento – SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Please check with catalog_product_entity table.

I check with this table its belongs to CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

I already post my answer here - Magento 2 Cannot add or update a child row

Best Answer

Related Solutions

Magento 2.3 Database – Fix Integrity Constraint Violation for Duplicate Entry

Magento – SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Related Topic