15.00 - Key Values and Relationships Among Tables - Teradata Database

Teradata Database Design

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

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.

Example  

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:

 

employee

emp_num

emp_last_name

emp_first_name

emp_middle_initial

emp_phone

PK

 

 

 

 

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.