Temporal Relationship Constraints - Advanced SQL Engine - Teradata Database

Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
cjo1556732840654.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1182
lifecycle
previous
Product Category
Teradata Vantage™

A Temporal Relationship Constraint (TRC) is a referential relationship that is defined between a child table that does not have a valid-time column and a parent table that has a valid-time column. The FK of the child table must include a column, the TRC column, that references the valid-time column in the PK of the parent table. The value in the TRC column of the child table is constrained because it must exist within the time period defined by the valid-time column of the corresponding row of the valid-time table.

No special temporal syntax or qualifiers is required to create a TRC. Use the standard REFERENCES WITH NO CHECK OPTION syntax that is also used for creating other types of soft referential constraints. The difference is that for TRC the child table cannot have a valid-time column, and the parent table must have a valid-time column.

Because the parent table is a temporal table with valid-time, the value of the child table FK (excluding the TRC column value) can exist in more than one row of the parent table. In this case, the corresponding parent table rows must have non-overlapping, contiguous valid-time periods.

Like other temporal referential constraints, TRC is a soft constraint that is not enforced by the database. The primary reason to define TRC is to improve performance by allowing the Optimizer to eliminate redundant joins.

Examples of Temporal Relationship Constraints

The following statement creates a table and constrains the sale_date column value of each row to be a TIMESTAMP value that lies within the period defined by the valid-time column (vtcol) of the corresponding row in the parent valid-time table.

CREATE MULTISET TABLE sales (
  id INTEGER,
  description VARCHAR (100),
  sale_date TIMESTAMP(6),
  FOREIGN KEY (id, sale_date)
            REFERENCES WITH NO CHECK OPTION product(prod_id, vtcol)
) PRIMARY INDEX(id);

More than one TRC can be defined for a child table, but only one column can be the TRC column. In the case of the following example, this is the sale_date column of the child table:

CREATE MULTISET TABLE sales (
  id INTEGER,
  id2 INTEGER,
  description VARCHAR(100),
  sale_date TIMESTAMP(6),
  FOREIGN KEY (id, sale_date) REFERENCES WITH NO CHECK OPTION
                              product(prod_id, vtcol),
  FOREIGN_KEY (id2, sale_date) REFERENCES WITH NO CHECK OPTION
product(prod_id2, vtcol) ,
) PRIMARY INDEX(id);

When there are two DateTime columns in the foreign key, the one that corresponds to the parent table valid-time column becomes the TRC column. In the example below column 'c' will be treated as the TRC column:

CREATE MULTISET TABLE Parent_Table
(
 a INT,
 b INT,
 c DATE,
 vt PERIOD(DATE) NOT NULL AS VALIDTIME, d DATE
)
PRIMARY INDEX(a);

CREATE MULTISET TABLE Child_Table(
  a INT,
  b INT,
  c DATE,
  d DATE,
  FOREIGN KEY (b, c, d)
   REFERENCES WITH NO CHECK OPTION Parent_Table(b, vt, d)
);