17.05 - Adding a Transaction-Time Column - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference

The added transaction-time column is populated with the system default value of PERIOD(TEMPORAL_TIMESTAMP, UNTIL_CLOSED).

In addition to the rules for specifying a transaction-time column specified in CREATE TABLE/CREATE TABLE ... AS (Temporal Forms), the following rules apply when using ALTER TABLE to add a transaction-time column to an existing table:

  • The table cannot have a UPI.
  • If it does, first use ALTER TABLE table_name MODIFY NOT UNIQUE, which is described in SQL Data Definition Language, to modify the PI to be non-unique.
  • The table may have a NUPI, or it can have no primary index.
  • Existing CHECK constraints become current constraints in the transaction-time dimension. An error is reported if there are any other types of constraints. See Using Constraints with Temporal Tables.
  • Any join indexes defined on the table must be dropped before the table can be made a transaction-time table.
  • The table cannot be the subject table of an existing trigger.
  • Existing views, macros, or triggered action statements that reference the table but do not specify a transaction-time qualifier in the statement referencing the table must be modified to add a transaction-time qualifier.

If an executing stored procedure includes an SQL statement that references the table being altered, and no explicit qualifier is specified in the SQL, the compile time qualifier is applied to the SQL.

The row partitioning for the table cannot be altered to be partitioned on the added transaction-time column unless the table is empty.