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 | |
… | … | … |
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.
- 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.