15.00 - Creating Transaction-Time Tables - Teradata Database

Teradata Database Temporal Table Support

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

Creating Transaction-Time Tables

There are three ways to create a transaction-time table:

  • Create a new table as a transaction-time table
  • Add a transaction-time column to a nontemporal or valid-time table
  • Add a derived period column for transaction time to a table that uses two columns to represent the beginning and end of a duration that represents the transaction time for the row information.
  • Creating a New Transaction-Time Table

    To create a transaction-time table, use a normal CREATE TABLE statement, and define one column of the table as a transaction-time column.The data type of the column must be PERIOD(TIMESTAMP(6) WITH TIME ZONE), and must use the TRANSACTIONTIME or AS TRANSACTIONTIME column attribute. Transaction-time columns must also specify the NOT NULL column attribute.

    Example  

       CREATE MULTISET TABLE Policy_Types (
          Policy_Name VARCHAR(20),
          Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
          Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL 
             AS TRANSACTIONTIME
          )
       PRIMARY INDEX (Policy_Name);

    Adding a Transaction-Time Column to a Table

    To add a transaction-time column to a nontemporal or valid-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 transaction-time column to the Customer table:

       ALTER TABLE Customer 
       ADD Customer_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL 
          AS TRANSACTIONTIME;

    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 Transaction-Time Column Based on Two Existing Timestamp 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 transaction-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_Tx_Begin TIMESTAMP(6) WITH TIME ZONE,
          Policy_Tx_End TIMESTAMP(6) WITH TIME ZONE
          )
       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 transaction-time table using the following statement:

     ALTER TABLE Policy
      ADD PERIOD FOR Policy_Tx_ Duration (Policy_Tx_Begin,Policy_Tx_End) AS TRANSACTION TIME;

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

    Converting a Period Column to a Transaction-Time Column

    If you have a table that includes a column of type PERIOD(TIMESTAMP(6) WITH ZONE), take the following steps to convert the existing Period column to a transaction-time column:

    1 Note all the constraint information on the original table.

    2 Drop all the constraints on the original table.

    3 Grant NONTEMPORAL privilege to the user on the table.

    4 ALTER TABLE to add a transaction-time column.

    5 Submit NONTEMPORAL UPDATE to set the new transaction-time column with the existing column value being converted.

    6 ALTER TABLE to drop the existing Period column.

    7 ALTER TABLE to rename the transaction-time column with the name of the dropped column.

    8 Create all the previously dropped constraints with the desired transaction-time qualifier.

    Related Information

     

    For more information on...

    See...

    transaction-time periods

    “Transaction Time” on page 18

    CREATE TABLE (temporal form)

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

    ALTER TABLE (temporal form)

    “ALTER TABLE (Temporal Form)” on page 44

    UPDATE TABLE (temporal form)

    “UPDATE (Temporal Form)” on page 164