Referential Constraints - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
Referential integrity is supported only on the Block File System on the primary cluster, not on the Object File System.

The Optimizer can create significantly better query plans if referential relationships are defined between tables specified in the request. The Referential Constraint feature, also called soft referential integrity, allows you tp 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 users do not violate the defined Referential Constraints, even though the system does not enforce those constraints. Otherwise, you can produce incorrect results and corrupt your databases when Referential Constraints are defined on application-critical columns and appropriate care is not taken to maintain data integrity. See Validating the Integrity of Base Tables in a Referential Constraint Relationship. Specify Referential Constraints only when the potential for data corruption and query errors can be managed in a way that 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 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.
The potential for such disintegrity has the following implications:
  • SQL permits violations without warning.
  • FastLoad, MultiLoad, and Teradata Parallel Transporter permits violations against columns defined with Referential Constraints without warning.

Temporal Relationship Constraints can be specified for temporal tables.

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

Rules for Using Referential Constraints

Other than their not enforcing referential integrity, most of the rules for Referential Constraints are identical to those documented by FOREIGN KEY Constraints and REFERENCES constraints.

The exceptions are documented by the following set of rules that apply specifically to the specification and use of Referential Constraints.
  • You can specify Referential Constraints for both of the following constraint types.
    • FOREIGN KEY (FK_column_set) REFERENCES (parent_table_PK_column_set)
    • (NFK_column_set) REFERENCES (parent_table_AK_column_set)

      where NFK indicates non-foreign key and parent_table_AK_column_set indicates an alternate key in the parent table.

  • Though not enforced by the system, Referential Constraint references count toward the maximum of 64 references permitted for a table referenced as a parent.
  • INSERT, DELETE, and UPDATE statements are not permitted against tables with unresolved, inconsistent, or non-valid Referential Constraints. This rule is identical to the rule enforced for standard and batch RI.
  • The candidate key acting as the primary key in the referenced table in the constraint need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaration as a USI in the table definition.