17.05 - Adding a Transaction-Time Column - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

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.