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.
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.
This statement is a Teradata extension to the ANSI SQL:2011 standard.
- 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;