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, also referred to as soft referential integrity, permits you to take advantage of these optimizations without incurring the overhead of enforcing the suggested referential constraints.
- A row having a value for a referencing column can exist in a table even when no equivalent parent table value exists.
- A row can, in some circumstances, match multiple rows in its parent table when the referenced and referencing column values are compared. This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See Rules for Using Referential Constraints).
- Events that would have been flagged as referential integrity violations by SQL if RI were being enforced are not flagged, and those violations of integrity are permitted without warning.
- Similarly, events that would have been flagged as RI errors by FastLoad, MultiLoad, and Teradata Parallel Transporter are permitted against columns defined with Referential Constraints without warning.
Temporal Relationship Constraints are another form of Referential Constraint that can sometimes be specified for temporal tables. For information on temporal tables, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.
Referential Constraints and Temporal Relationship Constraints are a Teradata extension to the ANSI SQL:2011 standard.