15.00 - Foreign Key Constraints - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Foreign Key Constraints

Foreign key constraints permit you to specify referential primary key‑foreign key relationships between a unique column set in the current base table and an alternate key column set in a different base table. The FOREIGN KEY keywords are required for table‑level foreign key definitions but cannot be used for column‑level foreign key definitions;. If you specify FOREIGN KEY, then you must also specify a REFERENCES clause. Teradata Database uses referential integrity constraints to enforce referential integrity (see “The Referential Integrity Rule” on page 95 and “Inclusion Dependencies” on page 632) and to optimize SQL requests (see SQL Request and Transaction Processing). The overhead of enforcing standard referential integrity constraints is summarized in “Referential Integrity Constraint Checks” on page 653 and “Overhead Costs of Standard Referential Integrity” on page 653.

You can also use a foreign key definition to specify any of the following referential constraint types.

  • Standard Referential Integrity
  • Batch Referential Integrity
  • Referential Constraints
  • Temporal Relationship Constraints
  • Temporal tables can only be defined for so‑called “soft” referential integrity relationships, or Referential Constraints, and for Temporal Relationship Constraints, and do not permit foreign key constraints for Standard or Batch Referential Integrity. See ANSI Temporal Table Support and Temporal Table Support or for details.

    When you specify the REFERENCES WITH NO CHECK OPTION phrase, Teradata Database does not enforce the defined referential integrity constraint. This implies the following things about child table rows:

  • A row having a value, meaning that the implication is false if the referencing column is null, 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 the list of rules for Referential Constraints later in this topic for details.

    The various types have different applications as described in the following table:

     

    Referential Constraint Type

    Application

    Referential Integrity Constraint

    (see “CREATE TABLE” in SQL Data Definition Language)

  • Tests each individual inserted, deleted, or updated row for referential integrity.
  • If insertion, deletion, or update of a row would violate referential integrity, then AMP software rejects the operation and returns an error message.
  • Permits special optimization of certain queries.
  • Batch Referential Integrity Constraint

    (see “CREATE TABLE” in SQL Data Definition Language)

  • Tests an entire insert, delete, or update batch operation for referential integrity.
  • If insertion, deletion, or update of any row in the batch violates referential integrity, then parsing engine software rolls back the entire batch and returns an abort message.
  • Permits special optimization of certain queries.
  • Referential Constraint

    (see “CREATE TABLE” in SQL Data Definition Language)

  • Does not test for referential integrity.
  • Assumes that the user somehow enforces referential integrity in a way other than the normal declarative referential integrity constraint mechanism.
  • Permits special optimization of certain queries.
  • Temporal Relationship Constraint (TRC)

    (see ANSI Temporal Table Support and Temporal Table Support)

     

     

     

     

     

     

     

  • Does not test for referential integrity.
  • Assumes that the user somehow enforces referential integrity in a way other than the normal declarative referential integrity constraint mechanism.
  • Permits special optimization of certain queries.
  • TRC relationships can only be defined at the table level.
  • TRC relationships cannot be defined on self‑referencing tables.
  • Referential Constraints do not enforce primary key-foreign key constraints between tables, so they avoid the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints should be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.

    Caution:

    If referential integrity errors occur, data corruption can occur. Erroneous results can be returned if a DML request specifies a redundant RI join and the primary key‑foreign key rows do not match.

    For more information see “Inclusion Dependencies” on page 632 and “CREATE TABLE” in SQL Data Definition Language.

    The table on the following pages summarizes the differences among the different referential constraints.

     

    Referential Constraint Type

    CREATE/ALTER TABLE Definition Clause

    Description

    Does It Enforce Referential Integrity?

    Level of Referential Integrity Enforcement

    Join EliminationOptimizations?

    Derived Statistics Propagated Between Child and Parent Tables?

    Pros

    Cons

    Standard Referential Integrity Constraint

    REFERENCES

    ANSI/ISO SQL:2011 compliant.

    Integrity enforcement done using a Referential Index (see “Sizing a Reference Index Subtable” on page 866).

    Yes

    Row

    Yes

    Yes

  • ANSI/ISO compliant.
  • Logs RI violations in an error table.
  • Efficient for low volume updates.
  • Not efficient for medium to large updates because it is enforced one row at a time.

    Batch Referential Integrity Constraint

    REFERENCES WITH CHECK OPTION

    Teradata extension to ANSI/ISO SQL:2011 standard.

    All or nothing for update operations.

    Enforced using the following methods:

  • Joining new child table keys to parent table to ensure they exist.
  • Joining deleted parent table keys to child table to ensure they do not exist.
  • Yes

    Implicit transaction

    Yes

    Yes

    Efficient for medium to large updates because uses the Optimizer to determine best way to make the join.

  • Can be slower than regular RI for small updates.
  • Does not log RI violations in an error table.
  • Referential Constraint

    (“soft referential constraint”)

    REFERENCES WITH NO CHECK OPTION

     

    Teradata extension to ANSI/ISO SQL:2011 standard.

    No RI enforcement by Teradata Database. You must ensure the integrity of the relationship yourself.

    Not necessary to define a UNIQUE constraint for the primary or alternate key column set.

     

    No

     

    None

     

    Yes

     

    Yes

     

    No cost for enforcing RI or uniqueness.

  • Incorrect results or data corruption can occur if the RI constraint is not valid.
  • Not an issue if you are certain about the integrity of the relationship.
  • Temporal Relationship Constraint (“TRC constraint”)

  • For information on temporal relationship constraints, see ANSI Temporal Table Support and Temporal Table Support.
  • When you specify the REFERENCES WITH NO CHECK OPTION phrase, Teradata Database does not enforce the defined referential integrity constraint. This implies the following things about child table rows.

  • A row having a non‑null 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 the list of rules for Referential Constraints later in this topic for details.

    Referential Constraints do not enforce primary key-foreign key constraints between tables, so they avoid the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints should be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.

    Caution:

    If referential integrity errors occur, data corruption can occur. Erroneous results can be returned if a DML request specifies a redundant RI join and the primary key‑foreign key rows do not match.

    For more information see “Inclusion Dependencies” on page 632 and “CREATE TABLE” in SQL Data Definition Language.