CREATE TABLE/CREATE TABLE ... AS (Temporal Forms) | Teradata Vantage - 17.05 - Syntax - CREATE TABLE - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
Teradata Vantage™ - 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 document. For additional syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 , Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 , and Teradata Vantage™ - SQL Data Control Language, B035-1149.
[NONTEMPORAL] CREATE MULTISET [ GLOBAL TEMPORARY | VOLATILE ] TABLE
  [ database_name. | user_name. ] table_name
  [ , create_table_option [...] ]
  ( other_create_table_option [,...] )
  standard_table_options [;]
For descriptions of standard data types, column attributes, index and partition options, and other standard CREATE TABLE syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
other_create_table_option
{ column_definition |

  derived_period_column |

  table_level_definition |

  NORMALIZE [ ALL BUT ( ignored_column_list ) ]
    ON normalize_column [ ON OVERLAPS [ OR MEETS ] ]
}
column_definition
column_name {

  data_type [ column_attribute | column_constraint_attributes ] [...] |

  temporal_column
}
derived_period_column
PERIOD FOR derived_column ( begin_column, end_column )
    [ [AS] { VALIDTIME | TRANSACTIONTIME } ]
table_level_definition
{ unique_definition | reference_definition | check_definition }
column_constraint_attributes
[ CONSTRAINT name ] {

  [ time_option ] { CHECK ( boolean_condition ) | UNIQUE | PRIMARY KEY } |

  [ RI_time_option ] REFERENCES WITH NO CHECK OPTION
    table_name [ ( column_name ) ]
}
temporal_column
{ { PERIOD (DATE) | PERIOD ( [ ( precision ) ] [ WITH TIME ZONE ] ) }
    [ NOT NULL ] [AS] VALIDTIME |

  PERIOD ( TIMESTAMP(6) WITH TIME ZONE ) NOT NULL [AS] TRANSACTIONTIME
}
unique_definition
[ CONSTRAINT name ]
  [ time_option ]
  { UNIQUE | PRIMARY KEY }
  ( column_name [,...] )
reference_definition
[ CONSTRAINT name ]
  [ RI_time_option ]
  FOREIGN KEY
  ( column_name [,...] )
  REFERENCES WITH NO CHECK OPTION table_name [ ( column_name [,...] ) ]
check_definition
[ CONSTRAINT name ]
  [ time_option ]
  CHECK ( boolean_condition )
time_option
CURRENT TRANSACTIONTIME
  [ AND [ CURRENT | SEQUENCED | NONSEQUENCED ] VALIDTIME ]
RI_time_option
{ CURRENT | SEQUENCED | NONSEQUENCED } TRANSACTIONTIME
  [ AND [ CURRENT | SEQUENCED | NONSEQUENCED ] VALIDTIME ]
]
NONTEMPORAL
Specifies (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
Specifies that duplicate rows are allowed.
Temporal tables must be multiset tables.
GLOBAL TEMPORARY
Specifies that a temporary table definition be created and stored in the Data Dictionary for future materialization.
VOLATILE
Specifies 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
Specifies 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
Specifies 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
Specifies one or more data definition phrases that define the type and attributes of data that will be stored in the column.

See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for more information on data types and column attributes.

For information on Period data types, see Teradata Vantage™ - Data Types and Literals, B035-1143.

Column Constraint Attributes

For more information on constraints, see Using Constraints with Temporal Tables.

CONSTRAINT name
Specifies the optional name for a constraint.
Transactiontime and validtime options, if used together, can be specified in any order, separated by AND.
CURRENT TRANSACTIONTIME
Specifies that only rows that are open in transaction time are to be checked for constraint violations.
SEQUENCED TRANSACTIONTIME
Specifies 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.
The sequenced transactiontime constraint qualifier is valid only for the REFERENCES constraint.
NONSEQUENCED
TRANSACTIONTIME
Specifies 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.
The parent table cannot have a transaction-time column. The nonsequenced transactiontime constraint qualifier is valid only for the REFERENCES constraint.
CURRENT VALIDTIME
Specifies 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
Specifies that rows that are history, current, and future in valid time are to be checked for constraint violations.
NONSEQUENCED VALIDTIME
Specifies 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)
Specifies 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
PRIMARY KEY
Specifies 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.
REFERENCES table_name [(column_name)]
Specifies 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.

WITH NO CHECK OPTION
Specifies that referential integrity is not to be enforced for the specified primary key-foreign key (or unique) relationship.

Temporal Column Definitions

[AS] VALIDTIME
Specifies 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
Specifies 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
Specifies the name of the derived period column.
(begin_column,end_column)
Specifies 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
Specifies that the derived period column will serve as the valid-time or transaction-time column of a temporal table.
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.

CONSTRAINT name
Specifies the optional name for the constraint.
Transactiontime and validtime options, if used together, can be specified in any order, separated by AND.
CURRENT TRANSACTIONTIME
Specifies that only rows that are open in transaction time are to be checked for constraint violations.
CURRENT VALIDTIME
Specifies 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
Specifies that rows that are history, current, and future in valid time are to be checked for constraint violations.
NONSEQUENCED VALIDTIME
Specifies 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
PRIMARY KEY
Specifies 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
Specifies 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.

CONSTRAINT name
Specifies the optional name for the constraint.
Transactiontime and validtime options, if used together, can be specified in any order, separated by AND.
CURRENT TRANSACTIONTIME
Specifies that every value for the constrained column in the child table must exist in the open rows of the parent table.
SEQUENCED TRANSACTIONTIME
Specifies 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
Specifies 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.
The parent table cannot have a transaction-time column.
CURRENT VALIDTIME
Specifies 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
Specifies 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
Specifies 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.
The parent table cannot have a valid-time column.
FOREIGN KEY
Specifies a foreign key for the table.
column_name
Specifies a name for a column defined as part of the foreign key.
REFERENCES WITH NO CHECK OPTION
Specifies 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
Specifies the name of the referenced parent table used in the referential integrity constraint definition.
column_name
Specifies 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.

CONSTRAINT name
Specifies the optional name for the constraint.
Transactiontime and validtime options, if used together, can be specified in any order, separated by AND.
CURRENT TRANSACTIONTIME
Specifies that only rows that are open in transaction time are to be checked for constraint violations.
CURRENT VALIDTIME
Specifies 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
Specifies 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
Specifies 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)
Specifies 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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

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

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)
Specifies 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.
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
Specifies 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.
When normalization is performed on tables having transaction time, only open rows qualify for normalization.
ON OVERLAPS
Specifies 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
Specifies 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.