Temporal Referential Constraints for ANSI Valid-Time Tables | Teradata Vantage - 17.00 - Temporal Referential Constraints for ANSI Valid-Time Tables - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

Temporal referential constraints define a relationship between two tables whereby every value in the constrained column or columns (the foreign key (FK)) of the child table, which must include the valid-time column as part of the FK, must exist in the corresponding referenced columns (the primary key (PK)) of the parent table. Consequently, the valid-time value of every row in the child table must exist as a valid-time value in the parent table.

These constraints are not enforced by Teradata Database so are sometimes referred to as "soft referential integrity." Although these constraints are not enforced, the Optimizer can use them to eliminate redundant joins and improve query performance.
It is the responsibility of the user to ensure that these constraints are not violated.

Temporal referential constraints can be defined for valid-time period columns in temporal tables by specifying the valid-time derived period column names with the special PERIOD keyword.

Syntax

FOREIGN KEY ( fk_column_list, PERIOD fk_valid_time_period )
  REFERENCES WITH NO CHECK OPTION
  table_name ( pk_column_list, PERIOD pk_valid_time_period )
fk_column_list
The column name or the comma-separated list of columns that serve as the foreign key for the child table. These columns reference and correspond to the pk_column_list. Every value in the columns of the fk_column_list columns must exist in the corresponding columns of the pk_column_list.
fk_valid_time_period
The name of the valid-time derived period column in the child table.
REFERENCES WITH NO CHECK OPTION
Specifies that this relationship is a referential constraint, and is not enforced by the database.

WITH NO CHECK OPTION is a Teradata extension to ANSI.

table_name
The name of the table referenced by the foreign key.
pk_column_list
The column name or the comma-separated list of columns that serve as the primary key for the parent table.
pk_valid_time_period
The name of the valid-time derived period column in the parent table.

ANSI Compliance

This statement is a Teradata extension to the ANSI SQL:2011 standard.

Usage Notes

Because this is a relationship between valid-time columns, the referencing table and referenced table in a PK-FK temporal referential constraint relationship can be either a valid-time or bitemporal table. The table types do not need to match. For more information on bitemporal tables, see Working With ANSI Bitemporal Tables.

Example: Temporal Referential Constraint on an ANSI Valid-Time Table

CREATE MULTISET TABLE hire_contracts(
 h_eid INTEGER NOT NULL, 
 h_name VARCHAR(5) NOT NULL,
 h_terms VARCHAR(5),
 contract_start DATE NOT NULL,
 contract_end DATE NOT NULL,
 PERIOD FOR hire_period(contract_start,contract_end) AS VALIDTIME,
 PRIMARY KEY(h_eid, hire_period WITHOUT OVERLAPS)
 )
 PRIMARY INDEX(h_eid);

CREATE MULTISET TABLE employee_vt_ri (
 eid INTEGER NOT NULL,
 ename VARCHAR(5) NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE NOT NULL,
 PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME,
 UNIQUE(eid,job_dur WITHOUT OVERLAPS),
 FOREIGN KEY(eid,PERIOD job_dur) REFERENCES WITH NO CHECK OPTION
 hire_contracts(h_eid, PERIOD hire_period)
 )
 PRIMARY INDEX (eid);