ALTER TABLE Statement (ANSI System-Time Table Form) | Teradata Vantage - ALTER TABLE (ANSI System-Time Table Form) - Advanced SQL Engine

Purpose

Temporal syntax for ALTER TABLE allows you to create ANSI temporal system-time tables from existing tables.

Adding system-time to the table involves these ALTER TABLE operations:

Adding a SYSTEM_TIME derived period column to the table by specifying the columns that will serve as the beginning and ending bounds of the system-time period.
Adding or altering the columns that will serve as the beginning bound and ending bound of the system-time period. If these columns already exist in the table, special attributes must be added to them.
Adding system versioning to the table.

Syntax

Use the following ALTER TABLE syntax to create a system-time table by adding a system-time derived period column and the component start and end columns in a single ALTER TABLE statement:

ALTER TABLE table_name
  ADD PERIOD FOR SYSTEM_TIME ( sys_start, sys_end )
  ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW START
  ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL GENERATED ALWAYS AS ROW END [;]

ALTER TABLE table_name ADD SYSTEM VERSIONING [;]

A system-time table is not considered to be a temporal table, and is not afforded any special temporal behaviors until system versioning has been added to the table. Add system versioning to the system-time table using the following syntax in a separate ALTER TABLE statement:

ALTER TABLE table_name ADD SYSTEM VERSIONING [;]

table_name: The name of the system-time table. May include database qualifier.
PERIOD FOR SYSTEM_TIME: Creates the system-time derived period column.
sys_start: The name of the column that will store the beginning bound of the system-time period.
GENERATED ALWAYS AS ROW START: Required attribute for column that defines the beginning bound of system-time period.
sys_end: The name of the column that will store the ending bound of the system-time period.
GENERATED ALWAYS AS ROW END: Required attribute for column that defines the ending bound of system-time period.
SYSTEM VERSIONING: Required attribute for system-time temporal tables.

The ALTER TABLE statement components must be in the order shown, with the derived period column defined before the component start and end columns. There is no comma between the ADD clauses in this case.

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

Usage Notes

After a table has been converted to a system-versioned system-time table, most ALTER TABLE operations are not allowed. These tables are typically used for regulatory and compliance purposes, and modifications to the table structure could defeat those purposes. To restore a system-versioned system-time table to a nontemporal table involves dropping the system versioning, after which all normal ALTER TABLE operations are allowed.

For information on removing system versioning, see Dropping System Versioning and System-Time.

Example: ALTER TABLE to Convert a Nontemporal Table to an ANSI System-Time Table

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

CREATE MULTISET TABLE employee_systime (
 eid INTEGER NOT NULL,
 ename VARCHAR(10) NOT NULL,
 deptno INTEGER NOT NULL,
 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL,
 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
 ) PRIMARY INDEX(eid);

INSERT INTO employee_systime VALUES 
 (1001,'Sania',111,TIMESTAMP'2002-01-01 00:00:00.000000-08:00',
                   TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES 
 (1002,'Ash',333,TIMESTAMP'2003-07-01 12:11:00.000000-08:00',
                 TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES   (1003,'SRK',111,TIMESTAMP'2004-02-10 00:00:00.000000-08:00',
                 TIMESTAMP'2006-03-01 00:00:00.000000-08:00');
INSERT INTO employee_systime VALUES 
 (1004,'Fred',222, TIMESTAMP'2002-07-01 12:00:00.350000-08:00',
                   TIMESTAMP'2005-05-01 12:00:00.350000-08:00');
INSERT INTO employee_systime VALUES 
 (1005,'Alice',222,TIMESTAMP'2004-12-01 00:12:23.120000-08:00',
                   TIMESTAMP'2005-05-01 12:00:00.450000-08:00');
INSERT INTO employee_systime VALUES 
 (1004,'Fred',555, TIMESTAMP'2005-05-01 12:00:00.350000-08:00',
                   TIMESTAMP'9999-12-31 23:59:59.999999+00:00');
INSERT INTO employee_systime VALUES 
 (1005,'Alice',555,TIMESTAMP'2005-05-01 12:00:00.450000-08:00',
                   TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

An unqualified SELECT on the table returns all rows, regardless of whether the row is open or closed in system time, because this is not yet a temporal table:

SELECT * FROM employee_systime;

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002  Ash      333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005  Alice    222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1004  Fred     222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1005  Alice    555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004  Fred     555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1001  Sania    111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1003  SRK      111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00

Two ALTER TABLE statements can change the table into a system-time temporal table:

ALTER TABLE employee_systime
 ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
 ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
               GENERATED ALWAYS AS ROW START
 ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
               GENERATED ALWAYS AS ROW END;
ALTER TABLE employee_systime
 ADD SYSTEM VERSIONING;

Now an unqualified SELECT will show only the rows that are open in system time:

SELECT * FROM employee_systime;

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       333 2003-07-01 12:11:00.000000+00:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000+00:00 9999-12-31 23:59:59.999999+00:00
1001 Fred      222 2002-07-01 12:00:00.350000+00:00 9999-12-31 23:59:59.999999+00:00
1003 Alice     222 2004-12-01 00:12:23.120000+00:00 9999-12-31 23:59:59.999999+00:00

Special temporal qualifiers allow you to display closed rows from system-time tables. For more information see Querying ANSI System-Time Tables.

Dropping System Versioning and System-Time

System-versioned system-time tables are typically used for regulatory and compliance purposes, and for keeping a table-resident history of database operations on the table data. Consequently, most types of ALTER TABLE changes to these tables are not allowed. However, ALTER TABLE can be used to remove system versioning. After system versioning has been removed from a temporal table, the table becomes a regular nontemporal table, and all normal ALTER TABLE operations are permitted.

Use the following ALTER TABLE syntax to remove system versioning from a system-time table:

ALTER TABLE  your_system_time_table  DROP SYSTEM VERSIONING;

Where your_system_time_table is the name of a system-versioned system-time table.

Dropping the SYSTEM VERSIONING from a system-time table deletes all closed rows from the table, and makes the table a nontemporal table.

To drop the system-time columns, including the derived period column and its component beginning and ending bound TIMESTAMP columns, use the following ALTER TABLE syntax:

ALTER TABLE  your_system_time_table  DROP PERIOD FOR SYSTEM_TIME;

Where, again, your_system_time_table is the name of a system-versioned system-time table.

Dropping the system-time derived period column will automatically drop the two component columns.

You must drop the SYSTEM VERSIONING from a system-time table before you can drop the SYSTEM_TIME derived period column and component columns.