0

I applied commands to my database in SSH (Putty) and my problem is solved. But a number of commands are in error: ERROR 1701 (42000): Can not truncate a table referenced in a foreign key constraint (db_name.sales_payment_transaction, CONSTRAINT FK_DA51A10B240XXXXXXXXXXXX FOREIGN KEY (payment_id) REFERENCES db_name.sales_flat_order_payment (entity_id))

Problem with:

TRUNCATE `sales_flat_creditmemo`; 
TRUNCATE `sales_flat_invoice`; 
TRUNCATE `sales_flat_order`; 
TRUNCATE `sales_flat_order_item`; 
TRUNCATE `sales_flat_order_payment`; 
TRUNCATE `sales_flat_quote`; 
TRUNCATE `sales_flat_quote_address`; 
TRUNCATE `sales_flat_shipment`; 
TRUNCATE `downloadable_link_purchase

What could be the problem? Thanks for any advice!

Full solution is below:

SET FOREIGN_KEY_CHECKS=0;

##############################
# SALES RELATED TABLES
##############################
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;
TRUNCATE `sales_flat_order`;
TRUNCATE `sales_flat_order_address`;
TRUNCATE `sales_flat_order_grid`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sales_flat_order_payment`;
TRUNCATE `sales_flat_order_status_history`;
TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;
TRUNCATE `sales_invoiced_aggregated`;           
TRUNCATE `sales_invoiced_aggregated_order`;       
TRUNCATE `log_quote`;

ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;

#########################################
# DOWNLOADABLE PURCHASED
#########################################
TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;

ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

#########################################
# RESET ID COUNTERS
#########################################
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;
2

This is because you have foreign key constraints on your database.

One thing you can do is, put the sql command SET FOREIGN_KEY_CHECKS = 0; at the starting of your code and then revert it back using SET FOREIGN_KEY_CHECKS = 1; at the end of your code.

I would recommend instead of directly executing a script to delete orders, you can use one of the free extension available for clearing order. The link of one of them is Order Delete

[EDIT]

To reset the Order No you can execute following queries in your database

1) UPDATE eav_entity_store SET increment_last_id = '000000000' WHERE entity_type_id = '5';

2) UPDATE eav_entity_store SET increment_last_id = '000000000' WHERE entity_type_id = '6';

3) UPDATE eav_entity_store SET increment_last_id = '000000000' WHERE entity_type_id = '7';

4) UPDATE eav_entity_store SET increment_last_id = '000000000' WHERE entity_type_id = '8';

Here

5 is for Orders 6 is for Invoice 7 is for Credit Memo 8 is for Shipment

Please check the article Order Id reset

  • Thank you! I need: 1) completely remove all orders from the Database AND 2) new order will have to start with a number # 100000001 (this is important) IF USE "Seamless Delete Order"(experience) + # RESET ID COUNTERS TRUNCATE eav_entity_store; ALTER TABLE eav_entity_store AUTO_INCREMENT=1; I will reach the goal? – user9714 Aug 7 '14 at 5:49
  • check the edited answer... – Dexter Aug 7 '14 at 6:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.