Corrective Constraints

A corrective constraint is a constraint that executes additional data manipulation in response to data manipulation initiated by a user.

One purpose of corrective constraints is to perform an operation that is fully automated, for example, a price calculation. Each time a user changes any of the input data used by a price calculation, the price is automatically recalculated.

Another purpose of corrective constraints is to solve a problem with input data supplied by the end user. By making sure this data is modified or supplemented so that it satisfies a rule, the corrective constraint creates data integrity where otherwise the user input would have to be refused. This is more user-friendly than to have a restrictive constraint block processing and issue an error message to the user.

For example, in travel, you can ensure that a guide's discount percentage is always at least 10% even if a user enters a lower percentage.

You define each corrective constraint as either an INSERT, UPDATE, or DELETE statement. The example could be written as:

UPDATE person

SET discount = 10

WHERE

(

discount < 10

OR discount IS NULL

)

AND guide = 'Y'

When you define a corrective constraint, you may optionally specify a message, but this is not typically necessary or good practice. If the constraint updates a record successfully, the message is shown as an information message.