Removing cascade deletion in the production environment to prevent data loss

When records are deleted in the staging environment for a database table that contains a cascade-delete foreign key relationship with another table and you propagate the changes to the production environment, data loss can occur. To prevent data loss, consider changing the foreign key relationship between the tables to be either a delete-restrict or a delete-no-action relationship.

About this task

When you select to delete a record, most database records are marked for delete and not immediately deleted. When a record is marked for delete, no cascade delete operation runs against any database tables that have a foreign key relationship to the table that includes the record marked for delete. If you disable the mark for delete operation during a load process, the actual deletion of a record and subsequent cascade deletion of other records occur.

When a record in a table that contains a cascade-delete foreign key relationship, such as the OFFER table is deleted in the staging environment and the deletion is propagated to the production environment, a cascade-delete operation might occur against records within ORDERITEMS table. Since there is no transactional ORDERS data within the staging environment, deleting OFFER data in the staging environment does not have a cascade effect within that environment. In the production environment, however, the cascade delete can occur. If a cascade-delete occurs in the production environment, any ORDERITEMS record that is associated with the deleted record in the OFFER table is also deleted. When the cascade deletion occurs, data loss can occur as records in the ORDERITEMS table are now missing for orders.

Procedure

To prevent data loss when deletions in the staging environment are propagated to the production environment, consider implementing one of the following options:
  • Convert the cascade-delete foreign key relationship between the OFFER and ORDERITEMS tables into a delete-restrict or delete-no-action relationship. The new relationship ensures that the offer record is not deleted within the production environment when any staging environment deletions are propagated.

    The stagingprop utility is enhanced to provide exit codes when a delete-restrict or delete-no-action violation occurs within the production environment. When you attempt to propagate deletions from the staging environment to the production environment, errors can occur. Enhancements to the stagingprop utility provide exit codes in the event of a delete-restrict violation.

    Run the following command to drop the cascade-delete foreign key relationship and create a cascade-restrict relationship:
    • DB2WC_installdir\migration\schema\db2\fixpack8\wcs.f_506.delrestrict.sql
    • OracleWC_installdir\migration\schema\oracle\fixpack8\wcs.f_506.delrestrict.sql
    • For IBM i OS operating systemWC_installdir\migration\schema\os400\fixpack8\wcs.f_506.delrestrict.sql
    Note: You can encounter a performance issue when your drop and create a foreign key relationship on tables that contain large amounts of data. The performance issue occurs because the foreign key relationship must be dropped and created row-by-row.
  • Use the mark-for-delete command for deleting table records for tables with cascade-delete foreign key relationships to tables with data that must be preserved.

    When you use the database cleanup utility in the staging environment to remove marked for delete records, data loss can occur when you propagate the changes to the production environment. For more information about running the database cleanup utility when your site uses a staging environment, see Running the Database Cleanup utility in a staging environment.

    For more information about the SQL for running the database cleanup utility for WebSphere Commerce objects, see Database Cleanup utility objects.

    Note: Avoid issuing delete commands, other than mark for delete, against tables that contain cascade-delete foreign key relationships with tables that include data that must be preserved. If you must run delete operations on a table, review the foreign key relationships for the table to identify the tables that are affected by the operations. For more information about the WebSphere Commerce database tables, see WebSphere Commerce database schema.