15.00 - Referential Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Referential Constraints

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.

  • 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” on page 566).
  • 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 ANSI Temporal Table Support andTemporal Table Support.

    Referential Constraints and Temporal Relationship Constraints are a Teradata extension to the ANSI SQL:2011 standard.