Relevant Values

Previous Next

See Also:

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.