17.10 - Referential Constraints - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.

The Referential Constraint feature presumes a high level of trust between Vantage and its users, assuming that users do not 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. 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. For an example, see Scenario for Data Corruption With Referential Constraints.
Referential Constraint relationships are defined by specifying the WITH NO CHECK OPTION phrase for a FOREIGN KEY … REFERENCES constraint. When you specify this phrase, Vantage does not enforce the defined RI constraint. This implies the following things about child table rows.
  • 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).
The potential for such disintegrity has the following implications.
  • 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.