ALTER TABLE (ANSI Valid-Time Table Form) - Teradata Database

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:

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

Syntax Element	Description
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...[Modifying Temporal Tables chapter]\ Note: [AS] VALIDTIME is a Teradata Database 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. Note: WITHOUT DELETE is a Teradata Database extension to ANSI.

ANSI Compliance

This is ANSI SQL:2011 compliant, but includes non-ANSI Teradata Database 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” on page 64.

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” on page 64 and “Modifying Rows in ANSI Valid-Time Tables” on page 68.