To maximize the usefulness of join elimination, you can specify Referential Constraints that Teradata Database 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, Teradata Database does not enforce the Referential Constraints that you define. This means that 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, Teradata Database does not return error messages that would otherwise occur when RI constraints are violated.
If you specify a column name for a Referential Constraint, it need not refer to the single column PK of the referenced table or to a single column alternate key in the referenced table defined as UNIQUE, though best practice dictates that it should. 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 always be unique even if it is 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 ensure that data integrity is maintained.
Specifying Referential Constraints relies heavily on your knowledge of your data. If the data does not actually 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 Teradata Database can return corrupted result data.