Primary Key Substitution of Manipulated Record

See Also:

When a constraint key query is issued, the Rules Engine adds primary key information of the record currently manipulated to the constraint key query SQL statement.

This is because indexes can be used if primary key columns are mentioned in a SQL statement.

Example

Consider the business rule: "A guide cannot guide more than one tour at the same time". This rule is implemented by a restrictive constraint:

SELECT    '' violation

FROM    schedtour st1

       ,schedtour st2

WHERE    st1.guide = st2.guide

AND    st1.schedtour_id != st2.schedtour_id

AND    st1.start_date BETWEEN st2.start_date AND st2.return_date

In this constraint, there is no join based on relationship columns (because there is no relationship). When a manipulation takes place on the SCHEDTOUR table, a constraint key query is issued to retrieve the primary key values of the driving table (SCHEDTOUR st1). These primary key values are substituted in the constraint test at commit time.

Using primary key information of the record currently manipulated, the SQL statement of the constraint key query is:

SELECT   t1.SCHEDTOUR_ID

FROM    OPS$CP2.SCHEDTOUR t1

WHERE   (t1.GUIDE = TO_NUMBER(:1)

        AND t1.SCHEDTOUR_ID <> TO_NUMBER(:2)

      AND t1.START_DATE BETWEEN

TO_DATE(:3, 'SYYYY/MMDDHH24MISS') AND

TO_DATE(:4, 'SYYYY/MMDDHH24MISS')

)

(5,127,2000/0101000000,2000/0101000000)

This SQL statement will perform much faster than without the primary key information of the record currently manipulated. This is because with primary key information, indexes can be used. This especially increases performance if the SCHEDTOUR table contains lots of records.