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 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.
- 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.