17.05 - Temporal Referential Constraints - Teradata Database

Teradata Vantage™ - Temporal Table Support

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

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 must exist in the corresponding referenced columns (the primary key (PK)) of the parent table. When a referential constraint involves temporal tables, the relationship can also be defined with respect to time.

Temporal referential constraints are not enforced by Teradata Database so are referred to as “soft” referential integrity. Definitions of these constraints must be include WITH NO CHECK OPTION on the child column REFERENCES constraint, and no uniqueness is enforced on the referenced parent table columns. 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. For more information and examples of validating and enforcing these constraints, see Enforcing and Validating Temporal Referential Constraints.

The following table describes the different types of temporal referential constraints. Note that current time for valid-time columns is the value of TEMPORAL_TIMESTAMP or TEMPORAL_DATE. For more information, see Timestamping.

Temporal Referential Constraint Form Description
CURRENT TRANSACTIONTIME Only open rows of the child and parent tables are considered.

Every FK value in the open rows of the child table must exist somewhere in the PK column of the open rows in the parent table.

CURRENT VALIDTIME Current and future rows of the child table are considered. History rows are not considered.

Every value in the child table FK column must exist in the parent table PK column for the period starting from current time through the entire future portion of the child row valid-time period.

If the child row value exists in more than one row in the parent table, the valid-time periods of these parent rows, when combined, must form a single, continuous period that includes current time, and extends through the entire future portion of the child row valid-time period.

SEQUENCED TRANSACTIONTIME Both open and closed rows of the child and parent tables are considered.

Every FK value in the child table must exist in the parent table PK column during the same period as the child-row transaction-time period.

If the child row value exists in more than one row in the parent table, the transaction-time periods of these parent rows, when combined, must form a single, continuous period that contains the entire transaction-time period of the child row.

SEQUENCED VALIDTIME History, current, and future rows of the child table are considered.

Every value in the child table FK column must exist in the parent table PK column during the same period as the child row valid-time period.

If the child row value exists in more than one row in the parent table, the valid-time periods of these parent rows, when combined, must form a single, continuous period that contains the entire valid-time period of the child row.

NONSEQUENCED VALIDTIME

or

NONSEQUENCED TRANSACTIONTIME

Ignores the time dimension, and behaves like a nontemporal referential constraint. Every FK value in the child table, must exist in the parent table:
  • For child tables with valid time, history, current, and future rows are considered.
  • For child tables with transaction time, open and closed rows are considered.

Nonsequenced referential constraints are only allowed between a temporal child table and a nontemporal parent, or between a temporal child table and a temporal parent that does not have the same kind of temporal column as the child table.

Example: Temporal referential constraint

Assume that a CURRENT VALIDTIME referential constraint is defined between the following two valid-time tables.

Col1 Col2 (FK) VTColA
100 5 ('2006/05/20', '2016/05/20')
ColA ColB (PK) VTColB
200 5 ('2006/07/20', '9999/12/31')

As is true for any referential constraint, every value in the constrained referencing (FK) column of the child table must exist in the referenced (PK) column of the parent table. However, because these are temporal tables, and the constraint is CURRENT VALIDTIME, the portion of the child row valid-time that begins at current time and extends through future time must be contained in the valid-time of the corresponding parent table row or rows.

Whether the constraint is violated depends on the current time when the row is inserted in the child table:
  • If TEMPORAL_DATE is 2006/11/20 at the time of the insertion, the constraint is not violated.

    The parent row valid-time period contains the portion of the child row valid-time period starting from current time.

    Although the valid-time of the parent row does not include the portion of the child row valid-time from 2006/05/20 through 2006/07/20), this is history, and the CURRENT VALIDTIME relational constraint does not consider history.

    The value in the parent table can exist in more than one row, provided that the valid-time periods of all such rows combine to contain the current and future portions of the child row valid time. The CURRENT VALIDTIME relational constraints would not be violated if the parent table included the following rows.

    ColA ColB (PK) VTColB
    150 5 ('2006/07/20', '2009/07/20')
    250 8 ('2004/07/20', '2005/07/20')
    350 5 ('2009/07/20', ‘2017/07/20’)
  • If TEMPORAL_DATE is 2006/06/20 at the time of the insertion, the constraint is violated.

    The corresponding parent rows do not include the current portion of the child row valid time from 2006/06/20 to 2006/07/20.

CURRENT and SEQUENCED referential constraints can be defined only between tables having the same types of time, valid time or transaction time. NONSEQUENCED referential constraints can be defined between a child table having the type of time specified in the constraint (VALIDTIME or TRANSACTION TIME) and a parent table that lacks the corresponding time dimension.

The following table summarizes the kinds of referential constraints that can be created between different parent and child table types.

Child Table Type Parent Table Type
Non-temporal (USI) Valid Time Transaction Time Bitemporal
Non-temporal R TRC with open parent rows R TRC
Valid Time NVT CVT

SVT

Invalid CVT

SVT

Transaction Time NTT NTT TRC CTT

STT

CTT TRC

STT TRC

Bitemporal NTT

NVT

NTT

CVT

SVT

CTT

STT

NVT

CTT

STT

CVT

SVT

Types of referential constraints represented in the table:

R=regular, nontemporal referential constraint

CVT=Current Valid Time

CTT=Current Transaction Time

SVT=Sequenced Valid Time

STT=Sequenced Transaction Time

NVT=Nonsequenced Valid Time

NTT=Nonsequenced Transaction Time

TRC=Temporal Relationship Constraint  (see Temporal Relationship Constraints)

Although temporal relational constraints are not enforced by Teradata Database, the following table describes the relationship that is assumed to exist between the type of temporal relational constraint on the child FK and the type of uniqueness constraint on the referenced parent columns.

Temporal Qualifier on Child Table Relational Constraint Assumed Temporal Qualifier on Parent Table PK/UNIQUE Constraint
CURRENT TRANSACTIONTIME CURRENT TRANSACTIONTIME or

SEQUENCED TRANSACTIONTIME

SEQUENCED TRANSACTIONTIME SEQUENCED TRANSACTIONTIME
NONSEQUENCED TRANSACTIONTIME Parent table cannot have a transaction-time column
CURRENT VALIDTIME CURRENT VALIDTIME or

SEQUENCED VALIDTIME

SEQUENCED VALIDTIME SEQUENCED VALIDTIME
NONSEQUENCED VALIDTIME Parent table cannot have a valid-time column

Examples: Sequenced validtime foreign key

The following example demonstrates a SEQUENCED VALIDTIME foreign key. The PrjAsgnmentDuration column holds the duration for which an employee is assigned to a project.

CREATE MULTISET TABLE employee
(
empid INTEGER,
address VARCHAR(200),
jobduration PERIOD(DATE) AS VALIDTIME
)PRIMARY INDEX ( empid );

CREATE MULTISET TABLE project
(
prjid INTEGER,
empid INTEGER,
PrjAsgnmentDuration PERIOD(DATE) AS VALIDTIME,
SEQUENCED VALIDTIME
       FOREIGN KEY(empid) REFERENCES
           WITH NO CHECK OPTION employee (empid)
)PRIMARY INDEX (prjid );

The following example demonstrates a SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME foreign key.

CREATE MULTISET TABLE employee
(
empid INTEGER,
address VARCHAR(200),
jobduration PERIOD(DATE) AS VALIDTIME,
tt PERIOD(TIMESTAMP(6) WITH TIME ZONE ) AS TRANSACTIONTIME NOT NULL
)
PRIMARY INDEX ( empid );

CREATE MULTISET TABLE project
(
prjid INTEGER,
empid INTEGER,
PrjAsgnmentDuration PERIOD(DATE) AS VALIDTIME,
tt PERIOD(TIMESTAMP(6) WITH TIME ZONE ) AS TRANSACTIONTIME NOT NULL,
SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME
       FOREIGN KEY(empid) REFERENCES
           WITH NO CHECK OPTION employee (empid)
)
PRIMARY INDEX (prjid );

The following example demonstrates altering an existing temporal table to have a SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME foreign key.

CREATE MULTISET TABLE employee
(
empid INTEGER,
address VARCHAR(200),
jobduration PERIOD(DATE) AS VALIDTIME,
tt PERIOD(TIMESTAMP(6) WITH TIME ZONE ) AS TRANSACTIONTIME NOT NULL
)
PRIMARY INDEX ( empid );

CREATE MULTISET TABLE project
(
prjid INTEGER,
empid INTEGER,
PrjAsgnmentDuration PERIOD(DATE) AS VALIDTIME,
tt PERIOD(TIMESTAMP(6) WITH TIME ZONE ) AS TRANSACTIONTIME NOT NULL
)
PRIMARY INDEX (prjid );

ALTER TABLE project add  SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME
       FOREIGN KEY(empid) REFERENCES
           WITH NO CHECK OPTION employee (empid) ;