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.
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
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:
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
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.