15.00 - ALTER TABLE (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

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

ALTER TABLE (Temporal Form)

Purpose  

Performs any of the following:

  • Adds a valid-time column or a transaction-time column or both to an existing table.
  • Adds, modifies, or drops columns from temporal tables.
  • Drops a valid-time column or a transaction-time column or both.
  • Adds, drops, or modifies constraints.
  • Syntax  

    Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

    For descriptions of standard data types, column attributes, and modifications relating to indexes, see SQL Data Definition Language.

     

    Syntax Element …

    Specifies …

    NONTEMPORAL

    that the table to be altered has transaction time and that the ALTER TABLE operation will modify data in the table, rather than save historical snapshots of existing rows before making changes.

    database_name.table_name

    the name of the table to alter and the optional name of the database or user in which it is contained if different from the current database.

    user_name.table_name

     

    table_name

     

    alter_table_option

    an option from the Alter Table Options list for a conventional ALTER TABLE statement.

    Column Changes Clause

    ADD column_name

    to add or change the specified column and its specified attributes.

    ADD and DROP cannot both be specified on the same column in the same ALTER TABLE statement.

    The ADD keyword either changes the definition of an existing column or adds a new column to the table. If the named column already exists, ADD indicates that its attributes are to be changed.

    Adding or changing a column on a table with transaction time requires that the statement include the NONTEMPORAL prefix.

    Data Type
    Column Attributes

    one or more data definition phrases that define data for the column.

    You must always specify a data type for a newly added column. See SQL Data Definition Language for more information on data types and column attributes.

    For information on Period data types, see “Period Data Types: Basic Definitions” on page 29.

    DROP column_name
    [IDENTITY]
    [WITHOUT DELETE]

    that the named column is to be removed from the table.

    Dropping a column on a table with transaction time requires that the statement include the NONTEMPORAL prefix.

    When the transaction-time column is dropped from a table, all closed rows are deleted from the table.

    When the valid-time column is dropped from a table, all the rows that are no longer valid are deleted from the table.

    The IDENTITY option removes the IDENTITY attribute from a column without dropping the column itself. For more information, see SQL Data Definition Language.

    The WITHOUT DELETE option allows the TRANSACTIONTIME column to be removed from the table without deleting the rows that have been closed in transaction time.

    Column Constraint Attributes

    For more information on constraints, see “Using Constraints with Temporal Tables” on page 82.

    CONSTRAINT name

    the optional name for a constraint.

    CURRENT TRANSACTIONTIME

    that only rows that are open in transaction time are to be checked for constraint violations.

    SEQUENCED TRANSACTIONTIME

    that any value for the constrained column in the child table must exist in the parent table during the time period for which it exists in the child.

    Note: The sequenced transactiontime constraint qualifier is valid only for the REFERENCES constraint.

    NONSEQUENCED TRANSACTIONTIME

    that any value for the constrained column in the child table must exist in the parent table. A nonsequenced constraint is like a nontemporal relational constraint, and ignores the transaction-time column in the child table.

    Note: The parent table cannot have a transaction-time column.

    The nonsequenced transactiontime constraint qualifier is valid only for the REFERENCES constraint.

    CURRENT VALIDTIME

    that only rows that are current and future in valid time are to be checked for constraint violations. History rows are not checked.

    [SEQUENCED] VALIDTIME

    that rows that are history, current, and future in valid time are to be checked for constraint violations.

    NONSEQUENCED VALIDTIME

    that rows that are history, current, and future in valid time are to be checked for constraint violations. Nonsequenced constraints treat the valid-time column as a non-temporal column, so are similar to constraints on non-temporal tables. They apply to all open rows of the table.

    CHECK (boolean_condition)

    a boolean conditional expression that must be true, or else the row violates the check constraint.

    Check constraints cannot be placed on valid-time or transaction-time columns.

    UNIQUE or
    PRIMARY KEY

    that during the qualified time, any given value in the constrained column will not exist in more than one row at any instant in time:

  • For a current constraint this means any current or future rows that have overlapping time periods cannot have the same value in the column.
  • For a sequenced constraint this means any history, current, or future rows that have overlapping time periods cannot have the same value in the column.
  • For a nonsequenced constraint this means that the value of the column is unique in every row in the table, irrespective of whether row time periods overlap. This is similar to a unique or primary key constraint in a non-temporal table.
  • In all cases, if the table has a transaction-time column, the constraint is applied only to rows that are open in transaction time.

    For a current or sequenced PRIMARY KEY or UNIQUE constraint defined on a temporal table, the valid-time column must be defined as NOT NULL.

    The PK or unique column cannot be a valid-time or transaction-time column.

    Because PK and unique constraints on temporal tables are implemented as system-defined join indexes, the constraint is not allowed if it would cause the maximum number of secondary indexes to be exceeded for the table.

    REFERENCES table_name [(column_name)]

    a primary key-foreign key (or unique) referential integrity constraint where table_name is the parent table.

    The column cannot be a valid-time or transaction-time column.

    WITH NO CHECK OPTION

    that referential integrity is not to be enforced for the specified primary key-foreign key relationship.

    Temporal Column Definitions

    [AS] VALIDTIME

    that the column to be added is a valid-time column.

    A valid-time column can be added only if its data type is PERIOD(DATE), PERIOD(TIMESTAMP[(n)]), or PERIOD(TIMESTAMP[(n)] WITH TIME ZONE).

    There can be no more than one valid-time column in a temporal table.

    The existing table cannot have a UPI and cannot have any unique, primary key, or referential integrity constraints if a valid-time column is added.

    [AS] TRANSACTIONTIME

    that the column to be added is a transaction-time column.

    A transaction-time column must have a data type of PERIOD(TIMESTAMP(6) WITH TIME ZONE).

    There can be no more than one transaction-time column in a temporal table.

    A DEFAULT clause cannot be specified for a transaction-time column.

    A transaction-time column must be defined as NOT NULL.

    The existing table cannot have a UPI and cannot have any unique, primary key, or referential integrity constraints if a transaction-time column is added.

    Derived Period Column Changes

    derived_column

    the name of the derived period column.

    (begin_column,end_column)

    the names of the DateTime columns that will serve as the beginning and ending bounds of the derived period column. The data types of the columns must be identical, and both must be defined as NOT NULL.

    The begin and end bound columns used for a derived period column that will function as a valid-time column must be of data type DATE or TIMESTAMP(n) WITH TIME ZONE, where n is the precision of the timestamp, and WITH TIME ZONE is optional.

    The begin and end bound columns used for a derived period column that will function as a transaction-time column must be of data type TIMESTAMP(6) WITH TIME ZONE.

    [AS] VALIDTIME
    [AS] TRANSACTIONTIME

    that the derived period column will serve as the valid-time or transaction-time column of a temporal table.

    Note: The begin and end columns of a derived period column cannot be included in a primary index if the derived period column serves as a valid-time or transaction-time column.

    Constraint Change Options

    For more information on constraints, see “Using Constraints with Temporal Tables” on page 82.

    ADD
    DROP
    ADD/DROP CONSTRAINT
    name

    to add or drop a constraint.

    When a named primary key or unique constraint is dropped, the associated system-defined join index or USI is automatically dropped.

    You cannot use ALTER TABLE to drop an unnamed primary key or unique constraint. Instead, use DROP JOIN INDEX to drop a system-defined join index, or DROP INDEX to drop a system-defined unique secondary index.

    Table Level REFERENCES Definition

    For more information on constraints, see “Using Constraints with Temporal Tables” on page 82.

    CONSTRAINT name

    the optional name for the constraint.

    CURRENT TRANSACTIONTIME

    that every value for the constrained column in the child table must exist in the open rows of the parent table.

    SEQUENCED TRANSACTIONTIME

    that any value for the constrained column in the child table must exist in the parent table during the time period for which it exists in the child.

    NONSEQUENCED TRANSACTIONTIME

    that any value for the constrained column in the child table must exist in the parent table. A nonsequenced constraint is like a nontemporal relational constraint, and ignores the transaction-time column in the child table.

    Note: The parent table cannot have a transaction-time column.

    CURRENT VALIDTIME

    that any value for the constrained column in the child table must exist in the open current or future rows of the parent table. The valid-time period of the child row must be contained within the combined valid-time periods of current and future rows in the parent table that have a value that matches the child table. History rows in the child table are not checked, and history rows in the parent table are not considered.

    [SEQUENCED] VALIDTIME

    that any value for the constrained column in the child table must exist in the open rows of the parent table. The valid-time period of the child row must be contained within the combined valid-time periods of open rows in the parent table that have a value that matches the child table.

    NONSEQUENCED VALIDTIME

    that any value for the constrained column in the child table must exist in the parent table. A nonsequenced constraint is like a nontemporal relational constraint, and ignores the valid-time column in the child table.

    Note: The parent table cannot have a valid-time column.

    FOREIGN KEY

    a foreign key for the table.

    column_name

    a name for a column defined as part of the foreign key.

    REFERENCES WITH NO CHECK OPTION

    an integrity reference to the parent table named in table_name.

    Referential integrity is not enforced for the specified primary key-foreign key (or unique) relationship

    table_name

    the name of the referenced parent table used in the referential integrity constraint definition.

    column_name

    a column in the column set that makes up the parent table PRIMARY KEY or UNIQUE candidate key columns. The column cannot be a valid-time or transaction-time column.

    DROP INCONSISTENT REFERENCES

    to delete all inconsistent references defined on the table.

    Table Level CHECK Definition

    For more information on constraints, see “Using Constraints with Temporal Tables” on page 82.

    CONSTRAINT name

    the optional name for the constraint.

    Note: Transactiontime and validtime options, if used together, can be specified in any order, separated by AND.

    CURRENT TRANSACTIONTIME

    that only rows that are open in transaction time are to be checked for constraint violations.

    CURRENT VALIDTIME

    that only rows that are current and future in valid time are to be checked for constraint violations. History rows are not checked.

    [SEQUENCED] VALIDTIME

    that rows that are history, current, and future in valid time are to be checked for constraint violations.

    NONSEQUENCED VALIDTIME

    that rows that are history, current, and future in valid time are to be checked for constraint violations. Nonsequenced constraints treat the valid-time column as a non-temporal column, so are similar to constraints on nontemporal tables. They apply to all open rows of the table.

    CHECK (boolean_condition)

    a boolean conditional expression that must be true, or else the row violates the check constraint.

    Check constraints cannot be placed on valid-time or transaction-time columns.

    Table Level UNIQUE Definition

    For more information on constraints, see “Using Constraints with Temporal Tables” on page 82.

    CONSTRAINT name

    the optional name for the constraint.

    Note: Transactiontime and validtime options, if used together, can be specified in any order, separated by AND.

    CURRENT TRANSACTIONTIME

    that only rows that are open in transaction time are to be checked for constraint violations.

    CURRENT VALIDTIME

    that only rows that are current and future in valid time are to be checked for constraint violations. History rows are not checked.

    [SEQUENCED] VALIDTIME

    that rows that are history, current, and future in valid time are to be checked for constraint violations.

    NONSEQUENCED VALIDTIME

    that rows that are history, current, and future in valid time are to be checked for constraint violations. Nonsequenced constraints treat the valid-time column as a non-temporal column, so are similar to constraints on nontemporal tables. They apply to all open rows of the table.

    UNIQUE and
    PRIMARY KEY

    that during the qualified time, any given values in the constrained columns will not exist in more than one row at any instant in time:

  • For a current constraint this means any current or future rows that have overlapping time periods cannot have the same value in the columns.
  • For a sequenced constraint this means any history, current, or future rows that have overlapping time periods cannot have the same value in the columns.
  • For a nonsequenced constraint this means that the value of the column is unique in every row in the table, irrespective of whether row time periods overlap. This is similar to a unique or primary key constraint in a non-temporal table.
  • In all cases, if the table has a transaction-time column, the constraint is applied only to rows that are open in transaction time.

    For a current or sequenced PRIMARY KEY or UNIQUE constraint defined on a valid-time table, the valid-time column must be defined as NOT NULL.

    The PK or UNIQUE columns cannot include the valid-time or transaction-time columns.

    Because PK and unique constraints on temporal tables are implemented as system-defined join indexes or unique secondary indexes, the constraint is not allowed if it would cause the maximum number of secondary indexes to be exceeded for the table.

    column_name

    a column in the column set to be used as the primary key or as unique. The column cannot be a valid-time or transaction-time column.

    Table Level NORMALIZE Definition

    For more information on the NORMALIZE option, see SQL Data Definition Language.

    NORMALIZE

    The rows are to be combined based on a specified period column. The values for all other columns must be identical for rows to be combined. The period column values for combined rows must overlap or meet, and are coalesced to result in a single time period that is the union of the time periods for the combined rows.

    The period column for normalization can be a derived period column.

    ALL BUT (ignored_column_list)

    A column name or comma-separated list of column names that are to be ignored for purposes of the normalization. When rows are inserted or updated in the table, the value of the ignored columns in the result is non-deterministic. These values will reflect values from rows that have been recently added to the table, but precise values cannot be predicted nor necessarily reproduced.

    Note: Although temporal tables with transaction time can be normalized, the values in the transaction-time column are likely to be unique. Therefore most table rows will not qualify for normalization unless the transaction-time column is included in the ignored_column_list.

    ON normalize_column

    The period or derived period column to be normalized. This cannot be a transaction-time column, but can be a valid-time or other period column.

    Note: When normalization is performed on tables having transaction time, only open rows qualify for normalization.

    ON OVERLAPS

    The normalize condition is that the period values for rows that will be coalesced must overlap. ON OVERLAPS OR MEETS is the default for NORMALIZE.

    OR MEETS

    The normalize conditions is that the period values for rows that will be coalesced must meet. ON OVERLAPS OR MEETS is the default for NORMALIZE.

    Adding a Valid-Time Column

    If a default value is specified for a new valid-time column, the column is populated with the specified value. Otherwise, the new column is populated as follows:

     

    IF the valid-time column has a …

    THEN the column is populated with a value of …

    PERIOD(DATE) data type

    PERIOD(TEMPORAL_DATE, UNTIL_CHANGED).

    PERIOD(TIMESTAMP) data type

    PERIOD(TEMPORAL_TIMESTAMP, UNTIL_CHANGED).

    The precision and time zone values are set depending on the data type of the new column.

    In addition to the rules for specifying a valid-time column specified in “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65, the following rules apply when using ALTER TABLE to add a valid-time column to an existing table:

  • If the table has a transaction-time column, the ALTER TABLE statement must specify the NONTEMPORAL prefix. This requires the NONTEMPORAL privilege on the table.
  • The table cannot have a UPI.
    If it does, first use
    ALTER TABLE table_name MODIFY NOT UNIQUE, which is described in SQL Data Definition Language, to modify the PI to be non-unique.
  • The table may have a NUPI, or it can have no primary index.
  • Existing CHECK constraints become current constraints in the valid-time dimension. An error is reported if there are any other types of constraints. See “Using Constraints with Temporal Tables” on page 82.
  • Any join indexes defined on the table must be dropped before the table can be made a valid-time table.
  • The table cannot be the subject table of an existing trigger.
  • Existing views, macros, or triggered action statements that reference the table but do not specify a valid-time qualifier in the statement referencing the table must be modified to add a valid-time qualifier.
  • If an executing stored procedure includes an SQL statement that references the table being altered, and no explicit qualifier is specified in the SQL, the compile time qualifier is applied to the SQL.

    The partitioning for the table cannot be altered to be partitioned on the added valid-time column unless the table is empty.

    Adding a Transaction-Time Column

    The added transaction-time column is populated with the system default value of PERIOD(TEMPORAL_TIMESTAMP, UNTIL_CLOSED).

    In addition to the rules for specifying a transaction-time column specified in “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65, the following rules apply when using ALTER TABLE to add a transaction-time column to an existing table:

  • The table cannot have a UPI.
    If it does, first use
    ALTER TABLE table_name MODIFY NOT UNIQUE, which is described in SQL Data Definition Language, to modify the PI to be non-unique.
  • The table may have a NUPI, or it can have no primary index.
  • Existing CHECK constraints become current constraints in the transaction-time dimension. An error is reported if there are any other types of constraints. See “Using Constraints with Temporal Tables” on page 82.
  • Any join indexes defined on the table must be dropped before the table can be made a transaction-time table.
  • The table cannot be the subject table of an existing trigger.
  • Existing views, macros, or triggered action statements that reference the table but do not specify a transaction-time qualifier in the statement referencing the table must be modified to add a transaction-time qualifier.
  • If an executing stored procedure includes an SQL statement that references the table being altered, and no explicit qualifier is specified in the SQL, the compile time qualifier is applied to the SQL.

    The row partitioning for the table cannot be altered to be partitioned on the added transaction-time column unless the table is empty.

    Dropping Temporal Columns

    Dropping any type of column from a transaction-time or bitemporal table requires the NONTEMPORAL privilege on the table, and the NONTEMPORAL qualifier to ALTER TABLE must be used.

    Temporal columns cannot be dropped if any constraints or a join index or a trigger are defined on the table.

    Temporal columns cannot be dropped if the table is partitioned on the temporal columns.

    When a transaction-time column is dropped, all closed rows (all history rows in the transaction-time dimension) are deleted from the table.

    When a valid-time column is dropped, all the rows that are no longer valid (all history rows in the valid-time dimension) are deleted from the table.

    Related Information

     

    For more information on...

    See...

    ALTER TABLE (regular form)

    SQL Data Definition Language.

    adding temporal columns to nontemporal tables

  • “Adding a Valid-Time Column to a Table” on page 33
  • “Adding a Transaction-Time Column to a Table” on page 36.
  • CREATE TABLE (temporal form)

    “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65.

    derived period columns

    SQL Data Definition Language.

    dropping temporal columns from tables

  • “NONTEMPORAL Privilege” on page 181.
  • “Example 21: Dropping a Valid-Time Column” on page 212.
  • “Example 22: Dropping a Transaction-Time Column” on page 213.
  • temporal table constraints

    “Using Constraints with Temporal Tables” on page 82.