17.10 - Adding a Transaction-Time Column to a Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

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

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.

Example: Creating a Transaction-Time Column Based on Two Existing Timestamp Columns

If a nontemporal table has two pre-existing TIMESTAMP(6) WITH TIME ZONE 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 these columns. The derived period column can serve as a transaction-time column, converting the table to a temporal table.

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.

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 NOT NULL,
      Policy_Tx_End TIMESTAMP(6) WITH TIME ZONE 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 transaction-time table using the following statement:

   ALTER TABLE Policy
     ADD PERIOD FOR Policy_Tx_Duration (Policy_Tx_Begin,Policy_Tx_End) 
      AS TRANSACTIONTIME;

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

Example: Adding a Period Data Type Transaction-Time Column

Consider the following nontemporal table definition that lacks duration data:

   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;

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.