15.00 - CREATE TABLE (Temporal Form)CREATE TABLE ... AS - Teradata Database

Teradata Database Temporal Table Support

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

CREATE TABLE (Temporal Form)
CREATE TABLE ... AS

Purpose  

Defines the column names, column data types and attributes, primary and secondary indexes, column level and table level constraints, and other attributes of a new temporal table.

The CREATE TABLE ... AS form (Copy Table Syntax) copies specified column definitions and optionally copies their data and statistics to a new table. It can be used to create a temporal or nontemporal table from a temporal source table. In these cases, the syntax for CREATE TABLE ... AS is identical to the syntax described in SQL Data Definition Language. Rules and usage considerations for using CREATE TABLE AS with temporal source tables are described in“CREATE TABLE ... AS and Temporal Tables” on page 77.

Syntax - CREATE TABLE

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 and column attributes, see SQL Data Definition Language.

 

Syntax Element …

Specifies …

NONTEMPORAL

(for Copy Table Syntax, CREATE TABLE AS only)
that all the transaction-time values are to be copied from the source table to a target table that has transaction time.

NONTEMPORAL

If the target table is not a table with transaction time, the NONTEMPORAL prefix is ignored.

MULTISET

that duplicate rows are allowed.

Note: Temporal tables must be multiset tables.

GLOBAL TEMPORARY

that a temporary table definition be created and stored in the data dictionary for future materialization.

VOLATILE

that a volatile table be created, with its definition retained in memory only for the course of the session in which it is defined.

database_name.table_name
user_name.table_name
table_name

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

create_table_option

an option from the Create Table Options list for a conventional CREATE TABLE statement.

Column Definition Clause

column_name

specifies the name of one or more columns, in the order in which they and their attributes are to be defined for the table.

Data Type
Column Attributes

one or more data definition phrases that define data for the 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.

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.

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.

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

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 nontemporal 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 the valid-time or transaction-time column.

    Unique and PK constraints on temporal tables are usually implemented as system-defined join indexes. (See“Indexes for Primary Key and Unique Constraints” on page 84.) The constraint is not allowed if it would cause the maximum permitted 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.

    The implications of temporal qualifiers used with referential constraints are more complex than those for other constraints. For more information, see “Temporal Referential Constraints” on page 85.

    WITH NO CHECK OPTION

    that referential integrity is not to be enforced for the specified primary key-foreign key (or unique) 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 Definitions

    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.

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

    Unique and PK constraints on temporal tables are usually implemented as system-defined join indexes. (See“Indexes for Primary Key and Unique Constraints” on page 84.) The constraint is not allowed if it would cause the maximum permitted 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 REFERENCES 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 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.

    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.

    This is the default temporal qualifier for constraints applied to valid-time columns.

    [SEQUENCED] VALIDTIME

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

    This is the default type of valid-time constraint applied if the VALIDTIME keyword is specified without a CURRENT, SEQUENCED, or NONSEQUENCED prefix.

    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 nontemporal 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 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, which can be a derived 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.

    Note: NORMALIZE is not valid with CREATE TABLE AS. However, if you are copying a table that is normalized, and no further column specifications are given in the CREATE TABLE AS statement, the resulting table copy will be normalized as the original.

    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 may 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. Normalization is applicable to all open rows

    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.

    Index Definition Clause

    PRIMARY INDEX

    the primary index definition.

    Note: Temporal tables may have primary indexes, but they cannot have UPIs. The primary index must be a NUPI.

    index_name

    an optional name for the index.

    primay_index_column

    a column in the column set that defines a primary index.

    Note: The primary index cannot include a valid-time or transaction-time column.

    PARTITION BY

    that the primary index is partitioned by the specified partitioning expression or expressions.

    partitioning_expression

    one or more SQL expressions used to define the row or column partition set to which a partitioned row or column is assigned.

    See Data Dictionary and SQL Data Definition Language for more information on row and column partitioning.

    NO PRIMARY INDEX

    that the table is defined to have no primary index.

    It is unnecessary to use NO PRIMARY INDEX with column-partitioned tables. CP tables are implicitly created without primary indexes.

    Temporal tables that are not column partitioned, can be created either with or without primary indexes.

    See Data Dictionary and SQL Data Definition Language for more information on tables with no primary indexes (NOPI tables).

    UNIQUE INDEX

    a secondary index definition.

    INDEX

     

    index_name

    an optional name for the index.

    ALL

    that the system should ignore the assigned case specificity for a column.

    index_column_name

    a column set whose values are to be used as the basis for a secondary index.

    ORDER BY VALUES

    value row ordering on each AMP by a single NUSI column.

    ORDER BY HASH

    hash row ordering on each AMP by a single NUSI column.

    oder_column_name

    a column in the INDEX column list that specifies the sort order used to store index rows.

    Temporary/Volatile Table Preservation Clause

    ON COMMIT DELETE ROWS
    ON COMMIT PRESERVE ROWS

    to delete or preserve the contents of a global temporary or volatile table when a transaction completes.

     

    Required Privileges

    The privileges required are the same as those required for a conventional CREATE TABLE statement.

    For the Copy Table syntax, if the CREATE TABLE statement specifies the NONTEMPORAL qualifier, the NONTEMPORAL privilege is also required on the target temporal table.

    Resource Consumption

    Because temporal DML statements may insert new rows into a table or logically delete rows from a table, a temporal table occupies more space than a nontemporal table. Additionally, tables with transaction time grow monotonically because rows are never physically deleted and removed from these tables (unless rows are explicitly removed using the NONTEMPORAL DELETE statement, which requires special privileges).

    The increased number of row operations means that the SELECT and DML statements on temporal tables tend to be more resource intensive when compared to nontemporal tables. Because constraints defined on temporal tables are time aware, constraint checking is also more resource intensive on temporal tables than on nontemporal tables.

    To mitigate the performance impacts from operations on temporal tables:

  • Row partition temporal tables. Use the partitioning expressions recommended in this section.
  • Define appropriate join indexes on temporal tables.
  • Derived Period Columns and CREATE TABLE

    In addition to the regular rules for the use of derived period columns, which are described in SQL Data Definition Language, the following rules and restrictions apply to the use of derived VALIDTIME and TRANSACTIONTIME columns in temporal tables.

  • The component columns of a derived period column used as a temporal column cannot be part of the primary index.
  • When a derived period column is defined as a TRANSACTIONTIME column, neither of the component columns can be set by the user to NULL, neither of the columns can have user-specified default value, and both must have a data type of TIMESTAMP(6) WITH TIME ZONE.
  • All rules for regular VALIDTIME and TRANSACTIONTIME columns apply also to the component columns of a derived period column used as a temporal column.
  • UNTIL_CHANGED can be specified as one of the values to be compressed by multivalue compression for end_column for a VALIDTIME derived period column.
  • If the TRANSACTIONTIME column is a derived period column, and if data for the begin_column or end_column for imported data contains leap seconds, the seconds portion is adjusted to 59.999999 with the precision truncated to the described precision for the input data. During this process, if the beginning and ending bounds of a transaction-time period become the same, Teradata Database generates an error.
  • For example, if the a begin_column value is TIMESTAMP ‘2006-12-31 23:59:59.999’ and the corresponding end_column value is TIMESTAMP ‘2006-12-31 23:59.60.123’, the ending bound value is adjusted to TIMESTAMP ‘2006-12-31 23:59.59.999’ which is the same as the beginning bound, so Teradata Database generates an error.

  • If the source table of a CREATE TABLE ... AS statement has any derived period columns, the target table that is created will have the same derived period columns.
  • If a SEQUENCED qualifier is specified in a CREATE TABLE...AS subquery, and the source is a temporal table with derived period columns for VALIDTIME or TRANSACTIONTIME, the resulting target table is a temporal table with a period data type column acting as a VALIDTIME or TRANSACTIONTIME column.
  • CREATE TABLE ... AS and Temporal Tables

    CREATE TABLE ... AS, the syntax for copying all or a portion of a table, can be used to create a temporal or nontemporal table from a source temporal table. The syntax for CREATE TABLE ... AS is identical, whether used with a temporal or nontemporal source table. The syntax is described fully in SQL Data Definition Language. However, note the following information, which is specific to using a temporal source table.

  • If an AS subquery of a temporal table is used without a temporal qualifier the default temporal qualifier set for the session is used for the subquery.
  • New columns defined for a table in the CREATE TABLE ... AS statement cannot be VALIDTIME or TRANSACTIONTIME columns.
  • If the source table is a temporal table with a valid-time column, the target table created is a temporal table that has a valid-time column. In this case the following apply:
  • If WITH DATA is specified in the CREATE TABLE ... AS statement, the valid-time column values are copied to the target table.
  • Target tables can be created with column- and table-level temporal constraints. For tables with valid time, primary key and unique constraints automatically create system-defined join indexes (SJIs). These types of constraints can be defined on target tables in CREATE TABLE ... AS statements only if the WITH NO DATA option is used. (Note that NONSEQUENCED VALIDTIME primary key and unique constraints act as for nontemporal table, and create unique secondary indexes, rather than SJIs.)
  • If the source table is a temporal table with a transaction-time column, the target table created is a temporal table that has a transaction-time column. In this case, the following apply:
  • The transaction-time values are copied to the target table only if the NONTEMPORAL qualifier is used with CREATE TABLE ... AS. (Note that the NONTEMPORAL privilege is required to use the NONTEMPORAL qualifier.) The NONTEMPORAL qualifier is ignored if the source table does not have a transaction-time column.
  • If the NONTEMPORAL qualifier is not used, the transaction-time values in the target table default to (TEMPORAL_TIMESTAMP,UNTIL_CLOSED).
  • Target tables can be created with column- and table-level temporal constraints. These constraints are applicable only to open rows. Target tables with transaction-time can only have primary key and unique constraints, if the WITH NO DATA option is used.
  • Related Information

     

    For more information on...

    See...

    CREATE TABLE (regular form), CREATE TABLE AS

    SQL Data Definition Language.

    creating temporal tables

    Chapter 3: “Creating Temporal Tables”.

    constraints and temporal tables

    “Using Constraints with Temporal Tables” on page 82.

    NONTEMPORAL qualifier and privilege

    “Nontemporal Operations” on page 25 and “NONTEMPORAL Privilege” on page 181.

    partitioning temporal tables

    “Partitioning Expressions for Temporal Tables” on page 78.

    transaction time and valid time

    “Transaction Time and Valid Time” on page 17.