Foreign Keys | Database Design | Teradata Vantage - 17.10 - Foreign Keys - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

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

A foreign key is an attribute set in one relation based on an identical attribute set that acts as a primary or alternate key in a different relation.

Foreign keys are a special form of Inclusion Compatibilities.

Foreign keys provide a mechanism for performing primary index joins, sometimes referred to as prime key joins.

Foreign keys are also used to maintain referential integrity among related tables in a database (see The Referential Integrity Rule).

Rules

  • Foreign key values are restricted to three types.
    • A mirror image, drawn from the same domain, of a primary or alternate key in an associated relation.
    • Wholly null
    • Partially null

      The partially null case applies to compound foreign keys only, where one or more columns of the key might be null while others might contain references to primary keys in other tables, which are, by definition, non-null.

    Because of the myriad problems nulls present in database management (see Designing for Missing Information), you should avoid creating foreign keys that are either wholly or partially null.

  • You cannot use BLOB or CLOB columns to define a physical foreign key or other database constraint (see Designing for Database Integrity).
  • You cannot define a foreign key for a global temporary trace table. See the information about CREATE GLOBAL TEMPORARY TRACE TABLE in Teradata Vantageā„¢ - SQL Data Definition Language Detailed Topics, B035-1184.