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

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™

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:

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:

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

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