ALTER TABLE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - 17.00 - ALTER TABLE (ANSI Valid-Time Table Form) - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

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

Purpose

Temporal syntax for ALTER TABLE allows you to create ANSI temporal valid-time tables from existing tables. It can be used to modify existing tables that may have already been created having DateTime columns that manually track the effective period during which information in the row is effective.

Adding valid time to the table involves these ALTER TABLE operations:
  • Adding or altering the two DateTime columns that will serve as the beginning and ending bounds of the valid-time period
  • Adding a valid-time derived period column to the table, and designating the derived period column VALIDTIME

Syntax

Use the following ALTER TABLE syntax to create a valid-time table by adding a valid-time derived period column:

ALTER TABLE table_name
  ADD PERIOD FOR valid_time_period ( valid_start, valid_end ) [AS] VALIDTIME [;]

Use the following syntax to drop a valid-time derived period column:

ALTER TABLE table_name
  DROP valid_time_period [ WITHOUT DELETE ] [;]
table_name
The name of the valid-time table. May include database qualifier.
valid_time_period
The name of the valid-time derived period column.
valid_start
The name of the column that will store the beginning bound of the valid-time period.
valid_end
The name of the column that will store the ending bound of the valid-time period.
[AS] VALIDTIME
Required to create valid-time tables in Teradata Database.
The [AS] VALIDTIME clause allows SELECT statements on valid-time tables to use special temporal qualifiers (AS OF, BETWEEN, FROM TO, CONTAINED IN), which ANSI does not support on valid-time tables. For more information see Valid-Time Modifications.
[AS] VALIDTIME is a Teradata extension to ANSI.
WITHOUT DELETE
Prevents automatic deletion of history and future rows when the valid-time derived period column is dropped from a table.
WITHOUT DELETE is a Teradata extension to ANSI.

ANSI Compliance

This statement is ANSI SQL:2011 compliant, but includes non-ANSI Teradata extensions.

Usage Notes

  • [AS] VALIDTIME is an extension to ANSI. It is required to create valid-time tables in Teradata Database. The [AS] VALIDTIME clause allows SELECT statements on valid-time tables to use special temporal qualifiers (AS OF, BETWEEN...AND, FROM...TO, CONTAINED IN), which ANSI does not support on valid-time tables. For more information, see Querying ANSI Valid-Time Tables.
  • Dropping the valid-time derived period column from a valid-time table deletes all history and future rows from the table unless the WITHOUT DELETE option is used.

Example: ALTER TABLE to Convert a Nontemporal Table to a Valid-Time Table

The following SQL creates a regular nontemporal table, and inserts some rows:

CREATE MULTISET TABLE employee_vt (
 eid INTEGER NOT NULL,
 ename VARCHAR(5) NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE NOT NULL
 ) PRIMARY INDEX(eid);

After rows have been inserted, an unqualified SELECT on the table returns all rows:

SELECT * FROM employee_systime;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1002 Ash    TA05 2003/01/01  2003/12/31
1005 Alice  TW10 2004/12/01  2005/12/01
1010 Mike   TW07 2015/01/01  2016/12/31
1005 Alice  PW11 2005/12/01  9999/12/31
1001 Sania  TW08 2002/01/01  2006/12/31
1004 Fred   PW12 2001/05/01  9999/12/31
1003 SRK    TM02 2004/02/10  2005/02/10


The following ALTER TABLE statement changes the table into a valid-time temporal table:

ALTER TABLE employee_vt
 ADD PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME;

Unlike for system-time tables, an unqualified SELECT of a valid-time table shows all rows in the table, regardless of whether their valid-time period is passed, current, or in the future:

SELECT * FROM employee_systime;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1002 Ash    TA05 2003/01/01  2003/12/31
1005 Alice  TW10 2004/12/01  2005/12/01
1010 Mike   TW07 2015/01/01  2016/12/31
1005 Alice  PW11 2005/12/01  9999/12/31
1001 Sania  TW08 2002/01/01  2006/12/31
1004 Fred   PW12 2001/05/01  9999/12/31
1003 SRK    TM02 2004/02/10  2005/02/09


Automatic temporal behavior is evident in valid-time tables when using temporal qualifiers for SELECT queries, and for UPDATE, and DELETE modifications. For more information, see Querying ANSI Valid-Time Tables and Modifying Rows in ANSI Valid-Time Tables.