Constraints

A constraint is a programmatic construct, typically a SQL statement, that defines some aspect of data integrity that corresponds to a business rule.

Examples

Example 1

In a car rental company, a business rule is that each driver must have a driving license. The corresponding data integrity is that no driver is allowed to be registered in the database unless a driving license ID is also known. This is expressed by the following constraint SQL:

SELECT    ''
FROM      customer c
WHERE     c.driver = 'Y'
AND       c.driving_license_id IS NULL

Example 2

A tour operator wants to qualify each passenger older than 70 automatically as a special needs customer. This is expressed by the following constraint SQL:

UPDATE  passenger p
SET     p.special_needs = 'Y'
WHERE   70 <
(
     SELECT     FLOOR(
                  ( b.book_date -
                       USFormatUSFormat.CharToDate( b.birth_date, 'DD-MON-YYYY' )
                ) / 365
     )
     FROM       booking b
     WHERE      b.id = p.booking_id
)

Constraint type

You can write a constraint that rejects any data manipulation that violates your business rule. For instance, if a user attempts to set a hire date that is earlier than the date of the job vacancy, you can write a constraint to reject this input. This type of constraint is called a restrictive constraint.

You can also write constraints that, instead of rejecting input, automatically perform some extra data manipulation in response to data input. For example, if the number of participants in a tour reservation changes, you can write a constraint to re-calculate the reservation price. This type of constraint is called a corrective constraint.

If your implementation requires external (non-USoft) processing, you can write a Java of C# component and let your constraint invoke this component. This is referred to as RDMI (Rules-Driven Method Invocation). RDMI constraints may be restrictive or corrective, or they may be invocative:

•If the effect is to ensure data integrity by blocking certain forms of data manipulation, the constraint is restrictive.

•If the effect is to ensure data integrity by performing extra data manipulation, the constraint is corrective.

•In other cases, the constraint is invocative. For example, a constraint that tells an e-mail server to send a message is an invocative constraint.

Constraint Scope

You can also classify constraints by scope. A constraint that checks whether a given column value is within a specified pool of values, only needs that column value to decide what must be rejected or corrected. This is called a domain constraint. A constraint that checks whether a particular date value has an acceptable date format is a domain constraint.

Constraints that have a wider scope than domain constraints are called table constraints. A constraint that calculates the price of a booking in a parent table by looking at the number of participants in a child table is a table constraint.

A constraint that checks integrity across attributes of an entity only needs column values of 1 record to decide what to do. It has single-record scope. A check that a booking's enddate is later, not earlier, than its startdate has single-record scope.

Constraints that have multi-record scope need to look at multiple records from different tables or from different instances of the same table. A price calculation requiring data from one or more other tables than the price table has multi-record and multi-table scope. A constraint checking that some employee's manager exists within the same Employee table has multi-record and single-table scope.

Also on this subject

Single-Record Constraints

Restrictive Multi-Record Constraints

Corrective Multi-Record Constraints

Transitional constraints

The Rules Engine also supports constraints that check the validity of a transition between an old and a new value. An example is "An amount cannot increase by more than 10% at a time". This is called a transitional constraint.

You can use the OLD() function in your SQL to refer to the old value, or you can set transition properties for the constraint. If you use the OLD() function or set transition properties (or both), you turn the constraint into a transitional constraint.

Also on this subject

Transitional Constraints

The OLD() Function

Transition Properties