Enforcing and Validating Temporal Referential Constraints | Teradata Vantage - Enforcing and Validating Temporal Referential Constraints - Analytics Database - Teradata Vantage

Because temporal referential constraints are all “soft RI”, meaning they are not enforced by Vantage, 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.