[NONTEMPORAL] CREATE MULTISET [ GLOBAL TEMPORARY | VOLATILE ] TABLE [ database_name. | user_name. ] table_name [ , create_table_option [...] ] ( other_create_table_option [,...] ) standard_table_options [;]
- 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.
- 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:
- 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.
- 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:
- 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.
- 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.
- 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.