Syntax - Advanced SQL Engine - Teradata Database

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] ALTER TABLE
  [ database_name. | user_name. ] table_name
  [, alter_table_option ] [,...]
  [ other_alter_table_option ] [,...] [;]

For descriptions of standard data types, column attributes, and modifications relating to indexes, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

other_alter_table_option

{ column_change |

  table_level_constraint_change |

  ADD NORMALIZE [ ALL BUT ( ignored_column_list ) ]
    ON normalize_column [ ON OVERLAPS [ OR MEETS ] ] |

  DROP NORMALIZE
}

column_change

{ ADD column_name {
    data_type [ column_attributes ] |
    column_attributes |
    column_constraint_attribute |
    temporal_column
  } |

  DROP column_name [IDENTITY] [ WITHOUT DELETE ] |

  RENAME old_column_name [AS] TO new_column_name |

  derived_period_column
}

table_level_constraint_change

{ table_level_reference_definition |

  ADD [ CONSTRAINT name ] [ time_option ] CHECK ( boolean_condition ) |

  DROP [ CONSTRAINT name ] CHECK |

  MODIFY [ CONSTRAINT name ] [ time_option ] CHECK ( boolean_condition ) |

  table_level_unique_definition
}

column_constraint_attribute

[ 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
}

derived_period_column

{ ADD PERIOD FOR derived_column ( begin_column, end_column )
  [ [AS] { VALIDTIME | TRANSACTIONTIME } ] |

  DROP [ PERIOD FOR ] derived_column
}

table_level_reference_definition

{ { ADD [ CONSTRAINT name ] | DROP }
    [ RI_time_option ] 
    FOREIGN KEY ( column_name [,...] )
    REFERENCES WITH NO CHECK OPTION table_name [ ( column_name [,...] ) ] |

    DROP INCONSISTENT REFERENCES
  }

time_option

CURRENT TRANSACTIONTIME
  [ AND [ CURRENT | SEQUENCED | NONSEQUENCED ] VALIDTIME ]

table_level_unique_definition

{ DROP CONSTRAINT name |

  ADD [ CONSTRAINT name ] time_option
    { UNIQUE | PRIMARY KEY } ( column_name [,...] )
}

RI_time_option

{ CURRENT | SEQUENCED | NONSEQUENCED } TRANSACTIONTIME
  [ AND [ CURRENT | SEQUENCED | NONSEQUENCED ] VALIDTIME ]
]

NONTEMPORAL

Specifies 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

Specifies 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

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

Column Changes Clause

ADD column_name: Used 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: Specifies 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 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.
DROP column_name [IDENTITY] [WITHOUT DELETE]: Specifies 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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.; 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.

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 non-temporal column, so are similar to constraints on non-temporal 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.

WITH NO CHECK OPTION

Specifies that referential integrity is not to be enforced for the specified primary key-foreign key 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 Changes

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.

Constraint Change Options

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

ADD DROP ADD/DROP CONSTRAINT name: Used 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.

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.
DROP INCONSISTENT REFERENCES: Specifies to delete all inconsistent references defined on the table.

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.
[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 non-temporal 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 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 non-temporal 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.

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 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. 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): 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 will 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.
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.