Compensating actions

When integrity constraints are imposed there is a tendency for update operations to become more complex and fragile. Sometimes multiple changes must be made in a single atomic transaction in order to satisfy the constraints.

One approach is to make complex update operations look easy by defining compensating actions. These are additional implicit updates that are performed by an update operation. The idea is to make an update more complex than it appears to be.

An example of a compensating action is a cascading delete: a foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.

Sometimes compensating actions are defined using triggered procedures. A triggered procedure cannot be called explicitly, and instead executes automatically in response to events such as insertions, updates or deletions on rows in a table.

A compensating action is also called a propagation constraint [].

The term propagation constraint seems unfortunate (one might hope the word constraint is reserved for the constraints declared on the database value). Also the term propagation constraint should not be confused with the term transition constraint [] which means something else.

Also, the term compensating action should not be confused with a compensating transaction [] which means something else.

It could be argued by the Principle of least astonishment [] that it isn't reasonable for an update operation on one base variable to implicitly update another.

It seems better for update operations to have fixed semantics, rather than allow user-defined compensating actions to be defined to modify their behaviour.

There is an alternative approach, which is generally superior, particularly when complex constraints need to be imposed. It is to instead impose constraints indirectly using non-injective functions on the base variables. Compensating actions tend to be laborious to define compared to using non-injective functions to impose constraints.