15.10 - ALTER TABLE (ANSI System-Time Table Form) - Teradata Database

Teradata Database ANSI Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
15.10
category
Programming Reference
featnum
B035-1186-015K

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:

    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:

     

    Syntax Element

    Description

    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.

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

    This 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” on page 34.

    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” on page 37.

    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.

    Note: 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 dervied period column will automatically drop the two component columns.

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