Referencing Relationships in SQL Statements with the RELATE Keyword

Previous Next

See Also

USoft has extended the SQL language with the RELATE keyword, which enables you to refer to relationships between tables without having to specify their matching columns. Internally, this functionality is used to display relationships in the SQL Definer.

The syntax 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'

Related Topics

The RELATE Keyword in Transitional Constraints