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.
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
../../com.ibm.commerce.developer.doc/concepts/csddatabase.html.