- 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.
- Data Type Column Attributes
- Specifies one or more data definition phrases that define data for the column.
- DROP column_name [IDENTITY] [WITHOUT DELETE]
- Specifies that the named column is to be removed from the table.
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.
- 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.
- 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.
- [AS] TRANSACTIONTIME
- Specifies that the column to be added is a transaction-time column.
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.
- [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.
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.
- 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.
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:
- 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.
- 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.