Key Values and Relationships Among Tables | Teradata Vantage - 17.10 - Key Values and Relationships Among Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

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: Basic Case

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:

emp_num emp_last_name emp_first_name emp_middle_initial emp_phone

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:

emp_num_FK_employee emp_phone_number emp_phone_comment
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, Vantage 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 Designing for Database Integrity for more information.

For more information, see The Referential Integrity Rule and Domains and Referential Integrity.