Temporal Referential Constraints for ANSI Bitemporal Tables | Teradata Vantage - Temporal Referential Constraints for ANSI Bitemporal Tables - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

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

ANSI Compliance

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

Usage Notes

  • The system-time derived period column cannot be included in the list of referencing or referenced columns.
  • The PK-FK constraint applies only to rows that are open in the system-time dimension.
  • 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.

Example: Temporal Referential Constraint on an ANSI Bitemporal 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_bitemporal_ri (
eid INTEGER NOT NULL,
ename VARCHAR(5) NOT NULL,
deptno INTEGER 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),
sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL
    GENERATED ALWAYS AS ROW START,
sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
    GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(sys_start,sys_end),
FOREIGN KEY(eid,PERIOD job_dur) REFERENCES WITH NO CHECK OPTION
hire_contracts(h_eid, PERIOD hire_period)
)
PRIMARY INDEX (eid) WITH SYSTEM VERSIONING;