Relevant Values

Evaluation of multi-record constraints is caused by manipulations on one of the tables identified in the constraint SQL statement. Restrictive constraints might result in violations to be added to the violation list.

If the foreign key value of a child record is changed, the old values and the new values of the user manipulation each have a relation to a different parent record. However, not all constraints are interested in both the old values as well as the new values, simply because one of the two will never result in a violation or reparation.

Most constraints are interested in the column values AFTER the manipulation has taken place. These constraints check the new situation.

Example 1

Consider the business rule: "A reservation cannot be booked once the scheduled tour has started". This rule is implemented by a restrictive constraint:

SELECT      '' violation

FROM        reservation r

,           schedtour st

WHERE       r.schedtour_id = st.schedtour_id

AND         r.book_date >= st.start_date

Suppose an existing reservation is changed from one scheduled tour (with schedtour_id 1) to another (with schedtour_id 2).

In this case the rule only needs to be checked for the new situation:

SELECT      ''

FROM        OPS$CP1.RESERVATION t1

WHERE   

(

            t1.SCHEDTOUR_ID = TO_NUMBER(:1)

   AND      t1.BOOK_DATE >= TO_DATE(:2, 'SYYYY/MMDDHH24MISS')

)

(2,2001/0111000000)

However, some constraints are interested in the values of the columns before the manipulation takes place. This happens when a constraint is NOT evaluated when a record exists, but IS evaluated when that record does not exist anymore.

Example 2

Consider the business rule: "A reservation must have a participant with an insurance". This rule is implemented by a restrictive constraint:

SELECT      '' violation

FROM        reservation r

WHERE NOT EXISTS

(

            SELECT    ''

            FROM      participant pt

            WHERE     r.res_id = pt.res_id

            AND       r.insurance = 'Y'

)

Suppose an existing participant is changed from one reservation (with res_id 1) to another (with res_id 2).

In this case the rule only needs to be checked for the old situation:

SELECT     ''

FROM       dual

WHERE NOT EXISTS

(

          SELECT    ''

          FROM      OPS$CP1.PARTICIPANT t1

          WHERE 

          ( 

                    TO_NUMBER(:1) = t1.RES_ID

            AND     t1.INSURANCE = 'Y'

          )

)

(1)

NOTE: In this SQL statement, current row binding is applied to the RESERVATION table, the driving table of the constraint. That is why the RESERVATION table is not mentioned in this constraint.

Of course, a combination of both is also possible: constraints that are interested in both the old values and new values.

Example 3

Suppose the total number of participants for a scheduled tour is stored in a participants column in the SCHEDTOUR table. This column is updated by a corrective constraint:

UPDATE      schedtour st

SET         participants =

(

      SELECT    COUNT(*)

      FROM      reservation r

      ,         participant pt

      WHERE     st.schedtour_id = r.schedtour_id

      AND       r.res_id=pt.res_id

)

The UPDATE of a reservation from one scheduled tour to another should cause new participants calculation for BOTH scheduled tours.

The principle of relevant values is that at run time, the subquery is analyzed to decide if the OLD values, the NEW values, or both are relevant for checking the restriction or performing the correction:

•If the constraint contains a group function or a GROUP BY or HAVING clause, both NEW and OLD values are relevant for the manipulation.

•If the subquery in the constraint contains a NOT EXISTS or a NOT IN clause, only the OLD values are relevant for the manipulation.

•In all other cases, only the NEW values are relevant for the manipulation.