15.00 - Using Constraints with Temporal Tables - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Using Constraints with Temporal Tables

Column or table level constraints defined on temporal tables can be associated with a time dimension, and are of three basic types:

 

Temporal Constraint Form

Description

CURRENT VALIDTIME

The constraint is applied to all current and future rows. History rows are not checked for constraint violations.

SEQUENCED VALIDTIME

The constraint is applied to all future, current, and open history rows and ensures that the constraint is not violated at any instant of time in the valid-time dimension.

NONSEQUENCED VALIDTIME

The constraint is applicable to all open rows in the table, and treats the valid-time column as a regular, nontemporal column. A nonsequenced constraint on a valid-time table is similar in semantics to a constraint defined on a nontemporal table.

For tables with transaction-time columns, nonreferential constraints are always considered to be current in the transaction-time dimension. These constraints are enforced only on rows that are open in transaction time. For bitemporal tables, constraints are enforced only on open rows that satisfy the valid-time constraint temporal qualifier.

Because constraints are not allowed on columns with a period data type, constraints are not allowed on valid-time or transaction-time columns.

Note: To ensure application portability to future ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.

Check Constraints

Check constraints ensure that the value in a constrained column always satisfies the boolean expression that was specified when the check constraint was defined.

  • CURRENT VALIDTIME CHECK constraints are enforced only on rows that are current and future in valid time. Consequently, modifications to rows that are history rows in valid time are not checked for constraint violations.
  • SEQUENCED VALIDTIME CHECK constraints are enforced on history, current, and future rows in valid time.
  • NONSEQUENCED VALIDTIME CHECK constraints treat the valid-time column as a nontemporal column, so act like a check constraint on a nontemporal table, and are enforced on all open rows of the table. This means nonsequenced and sequenced check constraints have identical effects on temporal tables.
  • Primary Key and Unique Constraints

    Primary key and unique constraints impose uniqueness on column values amongst the rows of a table.

  • CURRENT VALIDTIME PRIMARY KEY and CURRENT VALIDIMTE UNIQUE constraints ensure that the value for the constrained column in a row is unique for all instances of time from current time through the future. Current and future rows that have overlapping valid-time periods are prevented from having the same value in the constrained columns.
  • SEQUENCED VALIDTIME PRIMARY KEY and SEQUENCED VALIDTIME UNIQUE constraints ensure that the value for the constrained column in a row is unique for all instances of time, including history, current, and future. Any rows that have overlapping valid-time periods are prevented from having the same value in the constrained columns.
  • NONSEQUENCED VALIDTIME PRIMARY KEY and NONSEQUENCED VALIDTIME UNIQUE constraints treat the valid-time column as a nontemporal column. These constraints ensure that the value for the constrained column in a row is unique amongst all rows in the table. All open rows are prevented from having the same value in the constrained columns.
  • Note: Nonsequenced PK/unique constraints are identical to PK/unique constraints on nontemporal tables. These types of constraints are rarely useful on temporal tables, and are not recommended. Due to the near duplication of rows that happens automatically as rows are modified in temporal tables, a nonsequenced PK/unique constraint would be violated very quickly. The same situation is true for USIs applied to temporal tables, which are also not recommended.

    Example  

    Assume that the TEMPORAL_DATE is November 2, 2006 (2006/11/02) and a valid-time table is defined with columns: Col1, Col2, and VTCol where VTCol is a valid-time column. Assume further that a CURRENT VALIDTIME UNIQUE constraint is defined on Col2. The following row:

     

    Col1

    Col2 (unique)

    VTCol

    5

    24

    ('2006/10/20', '2007/10/20')

    does not violate the constraint with the row:

     

    6

    24

    ('2008/01/20', '9999/12/31')

    because the valid-time periods do not overlap. The same first row would violate the current unique constraint with the row:

     

    7

    24

    ('2007/09/20', '9999/12/31')

    because the time periods overlap from 2007/09/20 to 2007/10/20.

    If the table also had a transaction-time as the fourth column, the following row:

     

    Col1

    Col2

    VTCol

    TTCol

    8

    24

    ('2008/01/20', '9999/12/31')

    ('2006/09/20', '2006/09/25')

    would not violate the current constraint because the row is closed in transaction time (has an end date prior to UNTIL_CLOSED), so this row is not considered for constraint checking.

    Indexes for Primary Key and Unique Constraints

    Because of the way rows are duplicated as a result of modifications to temporal tables, most primary key and unique constraints defined on temporal tables are implemented by means of system-defined join indexes (SJIs). These indexes enforce the uniqueness on an appropriate subset of rows, according to whether the constraint is current, sequenced, or nonsequenced.

    Example  

    A current unique constraint on a bitemporal table causes an SJI to be created and maintained automatically from selected columns of the temporal table. The primary index of the SJI is the constrained column or columns of the temporal table. The valid-time and transaction-time columns are selected using an appropriately qualified WHERE clause that limits the rows in the index to current and future rows:

    CREATE JOIN INDEX tablename_TJInumber 
    AS SELECT ROWID, ConstrainedColumn, VTColumn, TTcolumn
    FROM tablename
    WHERE END(VTColumn >= CURRENT_DATE - INTERVAL '2' DAY
    AND   END(TTcolumn) IS UNTIL_CLOSED
    PRIMARY INDEX (ConstrainedColumn);

    INTERVAL ‘2’ DAY  is required because current rows could be inserted in a time zone that is up to two days prior to the date for the time zone in which the index is created.

    SJIs are created in the same database as the constrained temporal table. They are named automatically by the system using a naming convention of tablename_TJInumber, where tablename is the name of the temporal table for which a PK or unique constraint has been defined, and number is the index ID of the constraint, a unique number that identifies the SJI.

    Note: These indexes are created, maintained, and deleted automatically as needed by the system. They should not be directly modified or deleted.

    The PK or unique constraint is not allowed if the associated SJI would cause the maximum permitted number of secondary indexes to be exceeded for the table.

    As time passes, the values of CURRENT_DATE and CURRENT_TIMESTAMP differ from the values that were used when the SJI was created. Because of this, current and future rows in SJIs, over time, become history rows, and therefore no longer needed in the index to enforce the current constraint.

    Use the ALTER TABLE TO CURRENT statement periodically to update SJIs and PPIs created for temporal tables. ALTER TABLE TO CURRENT transitions history rows out the SJIs created for current primary key and unique constraints. For more information on ALTER TABLE TO CURRENT, see SQL Data Definition Language.

    Because nonsequenced constraints treat temporal columns as if they were nontemporal, a nonsequenced valid-time PK or unique constraint on a valid-time table is implemented automatically by making the constrained column a USI. For bitemporal tables, a PK or unique constraint must be limited to rows that are open in transaction time, so an SJI is used. The SJI uses a WHERE clause to select only the open rows from the transaction-time column.

    Note: Because identity columns are not allowed in join indexes, PK and unique constraints cannot be defined on identity columns in temporal tables.

    Temporal Referential Constraints

    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.

    Note: 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 “Appendix D Enforcing and Validating Temporal Referential Constraints” on page 225.

    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” on page 27.

     

     

    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  

    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” on page 90)

    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  

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

    Temporal Relationship Constraints

    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.

    Note: It is the responsibility of the user to ensure that these constraints are not violated. For more information and examples of validating these constraints, see “Appendix D Enforcing and Validating Temporal Referential Constraints” on page 225.

    Examples  

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

    Related Information

     

    For more information on...

    See...

    CREATE TABLE (temporal form)

    “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65

    creating temporal tables

    Chapter 3: “Creating Temporal Tables”

    join elimination

    SQL Request and Transaction Processing

    validating temporal referential constraints

    “Appendix D Enforcing and Validating Temporal Referential Constraints” on page 225