The RELATE keyword

The syntax of the RELATE keyword is:

RELATE <parent1> "<role1>" <child1> [,<parentn> "<rolen>" <childn>]

The advantage is that you do not have to synchronize these two tables in your SQL statements. Moreover, if the relationship changes (e.g. because columns are added/removed), you do not have to rewrite your constraints.

So instead of having to write:

SELECT       e.name

FROM         company c, department d, employee e

WHERE        c.id = d.company_id

AND          e.company_id = d.company

AND      

(

             e.dept_id = d.id

   OR  

   (

             e.dept_id IS NULL

       AND   d.id IS NULL

   )

)

AND          e.gender = 'F'

AND          c.id = 'USOFT'

You can write:

SELECT       e.name

FROM         company c, department d, employee e

RELATE       c HAS d, d "EMPLOYS" e

WHERE        e.gender = 'F'

AND          c.id = 'USOFT'

When using the RELATE keyword in transitional constraints, and primary key or foreign key values are changed run time, the Rules Engine substitutes both the OLD and NEW values of the changed record in this constraint. In specific cases, this default behavior may lead to performance problems, or may result in unexpected violations.

To make the behavior of RELATE in transitional constraints more explicit, you can specify whether you want to execute the constraint for the old values or the new values only, by explicitly adding OLD or NEW to the RELATE construct. These keywords can be used with the transition table:

•If NEW is specified, the join will be over the new values of the key.

•If OLD is specified, the join will be over the old values of the key.

•If neither NEW nor OLD is specified, the join will be over the old AND new values. This is similar to the default behavior of RELATE.

Example

SELECT    ''

FROM      child c, parent p

RELATE    p with NEW c

WHERE     p.col = 'Y'

This results in only the new foreign key values of CHILD being joined with PARENT. Contrast this with:

SELECT    ''

FROM      child c, parent p

RELATE    p with OLD c

WHERE     p.col = 'Y'

This results in only the old foreign key values of CHILD being joined with PARENT.

NOTES:

•You can only use OLD and NEW on ONE table, the transition table.

•You cannot use OLD and NEW in the SQL Definer.

•The OLD keyword has different behavior to the OLD() function.