Primary Key Substitution of Manipulated Record

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.