Shopware 5 to 6 Migration: Resolving Foreign Key Errors with Anonymized Customer Data

The Challenge: Foreign Key Errors with Anonymized Customer Data During SW5 to SW6 Migration

Migrating from Shopware 5 (specifically version 5.5.7) to Shopware 6 can present unique challenges, especially when dealing with historical data that has undergone GDPR-mandated anonymization. A common issue highlighted in the Shopware forum involves a persistent foreign key constraint violation during the migration of order data, specifically affecting the order_customer entity.

The problem manifests as SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row, preventing the successful migration of orders, order documents, and customer data. This error occurs because the Shopware 6 Migration Assistant attempts to link orders to customer IDs that no longer exist in the source Shopware 5 system. In many cases, these customer records were intentionally removed or anonymized due to data retention policies (DSGVO/GDPR).

The Specific Error Message:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or
update a child row: a foreign key constraint fails
(h60164_shopware6.order_customer, CONSTRAINT
fk.order_customer.customer_id FOREIGN KEY (customer_id) REFERENCES
customer (id) ON DELETE SET NULL ON UPDATE CASCADE)

Understanding the Root Cause

The core of the issue lies in how the Shopware 5 system handles customer data anonymization versus how the Migration Assistant expects data integrity. When customer data in SW5 is anonymized, the associated customer records are often completely removed. However, the corresponding orders might still reference these now non-existent customer_id values. While the foreign key constraint ON DELETE SET NULL on the order_customer.customer_id field in Shopware 6 suggests that a missing customer ID should simply result in a NULL value (similar to guest orders), the Migration Assistant does not seem to interpret this correctly during the 'Write' step. Instead, it attempts to migrate the invalid customer_id, leading to the integrity constraint violation.

Forum users noted that this behavior has been present since the initial versions of the Migration Assistant and is unlikely to change without significant updates to the migration logic.

The Solution: Assigning Dummy Customer Accounts in Shopware 5

The recommended workaround, as suggested by experienced community members, involves a pre-migration step within your Shopware 5 database. Before initiating the migration to Shopware 6, you must ensure that all orders have a valid customer reference, even if it's a placeholder.

Steps to Resolve:

1. Identify Affected Orders: In your Shopware 5 database, identify all orders that are linked to non-existent customer accounts. This typically involves querying the s_order table and checking for customer_id values that do not have a corresponding entry in the s_user table.
2. Create a Dummy Customer: Create a generic 'dummy' customer account in your Shopware 5 system. This account will serve as a placeholder for all anonymized orders.
3. Assign Dummy Customer via DB Command: Use a direct database command (SQL query) to update the customer_id for all identified orders, assigning them to the newly created dummy customer account. This ensures that every order has a valid customer reference, satisfying the Migration Assistant's requirements.

By proactively addressing these orphaned orders in Shopware 5, you can circumvent the foreign key errors and ensure a smoother migration process to Shopware 6, preserving your order history without compromising data integrity.