To maximize the usefulness of join elimination, you can specify Referential Constraints that Vantage does not enforce.
You can specify the REFERENCES WITH NO CHECK OPTION option to specify CREATE TABLE and ALTER TABLE statements with Referential Constraints, and the Optimizer can use the constraints without incurring the penalty of database-enforced referential integrity.
But when you use a REFERENCES WITH NO CHECK OPTION clause, Vantage does not enforce the Referential Constraints you define. Therefore, a row having a non-null value for a referencing column can exist in a table even if an equal value does not exist in a referenced column. When you specify Referential Constraints, Vantage does not return error messages when RI constraints are violated.
If you specify a column name for a Referential Constraint, best practice is to refer to the single column PK of the referenced table or to a single column alternate key in the referenced table defined as UNIQUE. The key acting as the PK in the referenced table need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.
The candidate key must be unique even if not explicitly declared to be so, otherwise the referential integrity can produce incorrect results, and corrupt your databases if you do not take care to make sure that data integrity is maintained.
Specifying Referential Constraints relies heavily on your knowledge of your data. If the data does not satisfy the Referential Constraints that you provide, then requests can easily produce incorrect results.
The Optimizer can use the Referential Constraints without incurring the penalty of database-enforced referential integrity, but with the likelihood that Vantage can return corrupted result data.