Because temporal referential constraints are all “soft RI”, meaning they are not enforced by Teradata Database, the responsibility for ensuring or validating the referential integrity is yours alone.
From the aspect of integrity assurance, the best way to guarantee the referential integrity of a table without taking advantage of a declarative standard or batch referential constraint is to use a procedural constraint such as a set of triggers to handle inserts, updates, and deletions to the tables in the relationship.
For example, you might want to create DELETE/UPDATE triggers on parent tables, and INSERT/UPDATE triggers on child tables to enforce referential integrity. The following example shows how an UPDATE trigger can be defined to enforce SEQUENCED referential integrity:
REPLACE TRIGGER trg_ri_validator AFTER SEQUENCED VALIDTIME UPDATE OF column_of_interest ON child_table REFERENCING NEW_TABLE as New1 FOR EACH STATEMENT BEGIN ATOMIC ( ABORT 'RI Violation' FROM ( NONSEQUENCED VALIDTIME SELECT foreign_key_column FROM child_table WHERE foreign_key_column IS NOT NULL AND foreign_key_column NOT IN ( SELECT parent_table.primary_key_column FROM parent_table WHERE parent_table.validtime_column CONTAINS child_table.validtime_column AND child_table.foreign_key_column = parent_table.primary_key_column ) ) derived_table_name; ) END;
For more information on the CREATE and REPLACE TRIGGER statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
The reasons for preferring declarative constraints over procedural constraints are described briefly in Teradata Vantage™ - Database Design, B035-1094. There is the additional likelihood that actively firing triggers will have a greater negative effect on system performance than the simple declarative constraint they are intended to replace.
If you decide not to enforce any form of referential integrity constraint, then you are strongly advised to enforce a set of validation procedures that can detect when and where referential integrity violations occur.
The following are suggested queries that can be used to validate temporal referential constraints in a child table.