Constraint Key Query

Previous Next

The Rules Engine can handle restrictive, non-transitional, multi-record constraints quickly if primary key values for the driving table records can be substituted in the constraint test SQL statement. This approach has two main advantages:

•At commit time, the constraint test can be executed faster, because the primary key values for the driving table record are known, and indexes can be used.

•It is possible to merge violations. If two initial row events raise the need to evaluate a constraint for the same driving table record, this evaluation only needs to be performed once.

This type of row binding, however, is only possible if the primary key values of the driving table are known. Therefore, at record validation time, the Rules Engine attempts to find these primary key values, and puts these values together with the constraints on the violation list.

In the determination of primary key values of the driving table there are four possible scenarios, that depend on both the triggering table and the constraint text:

1.The user manipulation is on the driving table. The primary key of the driving table is therefore immediately known.
2.The user manipulation is NOT on the driving table, but the primary key of the driving table can be determined directly.
An example is when a child record of the driving table is manipulated. The foreign key values of this child record are the primary key values of the driving table record.
3.The user manipulation is NOT on the driving table, and the primary key of the driving table cannot be determined directly.
In this case, a constraint key query is needed to retrieve the primary key values. This operation is identified in BenchMark profiler output as CONSTRAINT_KEYQUERY.
To optimize this constraint key query, the primary key of the manipulated record is substituted, except when the constraint is evaluated for more than one record (for example when group functions are involved ). See "Primary Key Substitution of Manipulated Record".
If the triggering table is not mentioned in the main query of the constraint, the Rules Engine also tries to calculate a path from the triggering table to the driving table. This calculated path is made explicit by query conditions used in the constraint key query statement.
4.A path to the driving table cannot be calculated. The constraint is tested without extra conditions.

If the Rules Engine can find the primary key values (scenarios 1, 2, and 3), the resulting primary key values for the driving table are substituted in the constraint test statement at commit time.

If the Rules Engine can NOT find the primary key values (scenario 4), then the constraint SQL statement is sent to the database "as is", that is, without primary key substitutions. Data integrity is safeguarded, but the constraint is checked against all data in the database.

Example 1:

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

SELECT    '' violation

FROM      schedtour st

,         reservation r

WHERE     st.schedtour_id = r.schedtour_id

AND       r.book_date >= st.start_date

 

The driving table of this constraint is SCHEDTOUR, because it is the first table in the FROM clause for which all primary key columns are mentioned.

•If the user manipulation is also on the SCHEDTOUR table, for instance the start date of a scheduled tour is changed, then the primary key of the driving table is immediately known.

•If the user manipulation is on RESERVATION, for instance a new reservation is made, then the primary key of the driving table can be determined directly via the join:

st.schedtour_id = r.schedtour_id

 

In both cases, no constraint key query is needed.

At commit time, the primary key value of the scheduled tour is substituted in the constraint test statement:

SELECT    ''

FROM      OPS$CP1.RESERVATION t1

WHERE   

(

          t1.SCHEDTOUR_ID = TO_NUMBER(:1)

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

)

(64,2001/0111000000)

 

Example 2

Consider the business rule "Participants for a SURVIVAL tour need to be insured". This business rule is implemented by a restrictive constraint:

SELECT    '' violation

FROM      schedtour st

,         reservation r

,         participant pt

WHERE     st.schedtour_id = r.schedtour_id

AND       r.res_id = pt.res_id

AND       pt.insurance = 'N'

AND       st.tour_type = 'SURVIVAL'

 

The driving table of this constraint is SCHEDTOUR, because it is the first table in the FROM clause for which all primary key columns are mentioned.

If the user manipulation is on PARTICIPANT, for instance when a new participant is added, then a path to the driving table can be determined via:

WHERE     st.schedtour_id=r.schedtour_id

AND       r.res_id=pt.res_id

 

A constraint key query is necessary here to retrieve the primary key values of the scheduled tours concerned.

SELECT     t1.SCHEDTOUR_ID

FROM       OPS$CP2.SCHEDTOUR t1

,          OPS$CP2.RESERVATION t2

WHERE   

(

           t1.SCHEDTOUR_ID = t2.SCHEDTOUR_ID

   AND     t2.RES_ID = TO_NUMBER(:1)

   AND     t1.TOUR_TYPE = 'SURVIVAL'

)

(200)

 

At commit time, the primary key value of the scheduled tour (128) is substituted in the constraint test statement:

SELECT     ''

FROM       OPS$CP1.RESERVATION t1

,          OPS$CP1.PARTICIPANT t2

WHERE      

(

           TO_NUMBER(:1) = t1.SCHEDTOUR_ID

   AND     t1.RES_ID = t2.RES_ID

   AND     t2.INSURANCE = 'N'

)

(128)

 

Example 3

Consider the business rule "A person must not be enrolled twice as participant on the same Scheduled Tour". This business rule is implemented by a restrictive constraint:

SELECT      '' violation

FROM        reservation r

,           participant pt

WHERE       pt.res_id = r.res_id

GROUP BY    r.schedtour_id

,           pt.person_id

HAVING      COUNT(*) > 1

 

In this case, the constraint is sent to the database without primary key substitutions. The Rules Engine does not build a CONSTRAINT KEY QUERY statement if the constraint SQL statement contains a GROUP BY clause or group functions, or if the SQL statement contains no primary key references.

See Also:

Primary Key Substitution of Manipulated Record