Adding a Valid-Time Column to a Table - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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.