17.10 - Adding a Valid-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 valid-time column to a nontemporal or transaction-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: Adding a Period Data Type Valid-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 valid-time column to the Customer table:

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

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.