In some circumstances, the Optimizer is able to create significantly better query plans if certain referential relationships have been defined between tables specified in the request. The Referential Constraint feature (this feature is sometimes referred to as soft referential integrity) permits you to take advantage of these optimizations without incurring the overhead of enforcing the suggested referential constraints.
The Referential Constraint feature presumes a high level of trust between Teradata Database and its users, assuming that users never violate any of the defined Referential Constraints even though the system does not enforce those constraints; otherwise, you can produce incorrect results and corrupt your databases in some situations when Referential Constraints are defined on application-critical columns and appropriate care is not taken to ensure that data integrity is maintained (see “Validating the Integrity of Base Tables In a Referential Constraint Relationship” on page 567). You should specify Referential Constraints only when the potential for data corruption and the possibility of query errors can be managed in such a way that it is not critical to your application. See “Scenario for Data Corruption With Referential Constraints” on page 569 for an example.
Referential Constraint relationships are defined by specifying the WITH NO CHECK OPTION phrase for a FOREIGN KEY … REFERENCES constraint. When you specify this phrase, Teradata Database does not enforce the defined RI constraint. This implies the following things about child table rows.
The potential for such disintegrity has the following implications.
Temporal Relationship Constraints are another form of Referential Constraint that can sometimes be specified for temporal tables. For information on temporal tables, see ANSI Temporal Table Support andTemporal Table Support.
Referential Constraints and Temporal Relationship Constraints are a Teradata extension to the ANSI SQL:2011 standard.