15.00 - Key Values and Relationships Among Tables - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Key Values and Relationships Among Tables

One result of database normalization is the identification of relationships among the tables defined in the database. These relationships are defined on the basis of primary key values shared between tables.


Consider the following basic case. The employee table was defined as a major entity during the logical design phase. Its columns are defined as follows:













The employee_phone table was defined as a minor entity, or entity subtype, related to the major entity, or entity supertype, employee table. Its columns are defined as follows:


Note that the employee_phone table has a foreign key. That foreign key, emp_num, is the primary key of the employee table. The two tables relate to one another through this primary key relationship.

In this particular relationship, the minor entity, employee_phone, is said to be the Child table because it references another table. That referenced table, employee, is said to be the Parent table in the relationship.

As a sidebar, note that because the referenced column is a primary key, it is by definition uniquely constrained, so it must be defined physically as either of the following two index types.

  • Unique primary index, NOT NULL.
  • Unique secondary index, NOT NULL.
  • Such a constraint only needs to be defined physically if the database management system enforces it. If the constraint is defined with a Referential Constraint, Teradata Database does not enforce the uniqueness on the parent table, so a UPI or USI is not required and the constraint is just assumed to be valid.

    Certain database semantics derive from such relationships, and those semantics require a particular type of database constraint to maintain their integrity. You cannot create referential constraints between columns typed as Period, XML, BLOB, or CLOB.

    This constraint is referred to as a referential constraint and it is said to maintain referential integrity.

    You cannot define database constraints of any kind on columns having the XML, BLOB, or CLOB data types. See Chapter 12: “Designing for Database Integrity” for more information.

    See “The Referential Integrity Rule” on page 95 and “Domains and Referential Integrity” on page 100 for more information about referential integrity.