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.
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);