15.00 - Creating Valid-Time Tables - Teradata Database

Teradata Database Temporal Table Support

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

Creating Valid-Time Tables

There are several ways to create a valid-time table:

  • Create a new table as a valid-time table
  • Add a valid-time column to a nontemporal or transaction-time table
  • Create a valid-time table from a copy of a nontemporal table
  • Creating a New Valid-Time Table

    To create a valid-time table, use a normal CREATE TABLE statement, and define one column as a valid-time column. The data type must be PERIOD(DATE), PERIOD(TIMESTAMP[(n)]), or PERIOD(TIMESTAMP[(n)] WITH TIME ZONE). Use the VALIDTIME or AS VALIDTIME column attribute to assign the column to be the valid-time column.

    Example  

       CREATE MULTISET TABLE Policy(
          Policy_ID INTEGER,
          Customer_ID INTEGER,
          Policy_Type CHAR(2) NOT NULL,
          Policy_Details CHAR(40),
          Validity PERIOD(DATE) NOT NULL AS VALIDTIME
          )
       PRIMARY INDEX(Policy_ID);

    Adding a Valid-Time Column to a Table

    To add a valid-time column to a nontemporal or transaction-time table, use the ALTER TABLE statement.

    Example  

    Consider the following nontemporal table definition:

       CREATE MULTISET TABLE Customer (
          Customer_Name VARCHAR(40),
          Customer_ID INTEGER,
          Customer_Address VARCHAR(80),
          Customer_Phone VARCHAR(12)
          ) 
       PRIMARY INDEX (Customer_ID);

    The following statement adds a valid-time column to the Customer table:

       ALTER TABLE Customer 
       ADD Customer_Validity PERIOD(DATE) AS VALIDTIME;

    Temporal tables cannot have unique primary indexes. If the original nontemporal table has a unique primary index, use ALTER TABLE to modify the primary index to nonunique prior to adding the temporal column. Uniqueness can be applied to a temporal table using other constraints. For more information, see “Using Constraints with Temporal Tables” on page 82.

    Creating a Valid-Time Column Based on Two Existing DateTime Columns

    If a nontemporal table has two pre-existing DateTime columns that represent the beginning and end bounds of a period of time, you can add a derived period column to the table based on the DateTime columns. The derived period column can serve as a valid-time column, converting the table to a temporal table.

    Note: The begin and end columns of a derived period column cannot be included in a primary index if the derived period column serves as a valid-time or transaction-time column.

    Example  

    Given a table that was originally created with the following DDL statement:

       CREATE MULTISET TABLE Policy(
          Policy_ID INTEGER,
          Customer_ID INTEGER,
          Policy_Type CHAR(2) NOT NULL,
          Policy_Details CHAR(40),
          Policy_Begin DATE NOT NULL
          Policy_End DATE NOT NULL
          )
       PRIMARY INDEX(Policy_ID);

    Prior to converting the table to a temporal table, existing constraints should be noted, then dropped. The table could be converted to a valid-time table using the following statement:

     ALTER TABLE Policy
      ADD PERIOD FOR Policy_Duration (Policy_Begin,Policy_End) AS VALIDTIME;

    After creating the temporal table, the constraints that were dropped can be reapplied.

    Converting a Period Column to a Valid-Time Column

    If you have a table that defines a Period column, take the following steps to convert the existing Period column to a valid-time column:

    1 Note all the constraint information on the table.

    2 Drop all the constraints.

    3 ALTER TABLE to add a new valid-time column.

    4 Submit NONSEQUENCED VALIDTIME update to set the new valid-time column with the existing period column value.

    5 ALTER TABLE to drop the existing Period column from the table.

    6 ALTER TABLE to rename the valid-time column with the name of the dropped column.

    7 Create all of the previously dropped constraints with the desired valid-time qualifier.

    Creating a Valid-Time Table as a Copy of a Nontemporal Table

    To create a valid-time table as a copy of an existing nontemporal table, use CREATE TABLE … AS (the copy table form of CREATE TABLE). Use the AS clause to specify a temporal query that returns a table with valid time.

    Example  

    Consider the following nontemporal table:

       CREATE TABLE Policy_NT (
          Policy_ID INTEGER,
          Customer_ID INTEGER,
          Policy_Type CHAR(2) NOT NULL,
          Policy_Details CHAR(40)
          )
       PRIMARY INDEX(Policy_ID);

    To create a copy of the Policy_NT table as a valid-time table, use a nonsequenced query in the AS clause of CREATE TABLE to specify a valid time period of applicability qualifier on the SELECT. The result is a valid-time table where the period of validity for every row is set to the period of applicability that was used in the query.

       CREATE MULTISET TABLE Policy(
          Policy_ID,
          Customer_ID,
          Policy_Type,
          Policy_Details,
          Validity
          ) AS (
          NONSEQUENCED VALIDTIME PERIOD '(2009-01-01, UNTIL_CHANGED)'
          SELECT *
          FROM Policy_NT)
       WITH DATA
       PRIMARY INDEX(Policy_ID);

    The resulting Policy table has a valid-time column named Validity:

       SHOW TABLE Policy;
     
       CREATE MULTISET TABLE Policy ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
          Policy_ID INTEGER,
          Customer_ID INTEGER,
          Policy_Type CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
          Policy_Details CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
          Validity PERIOD(DATE) AS VALIDTIME)
       PRIMARY INDEX ( Policy_ID );

    Related Information

     

    For more information on...

    See...

    valid-time periods

    “Valid Time” on page 19

    CREATE TABLE (temporal form)

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

    derived period columns

    “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65, and SQL Data Definition Language.

    ALTER TABLE (temporal form)

    “ALTER TABLE (Temporal Form)” on page 44

    UPDATE TABLE (temporal form)

    “UPDATE (Temporal Form)” on page 164