Temporal Referential Constraints - Advanced SQL Engine - Teradata Database

Example: Temporal referential constraint

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

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.

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.

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.

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