Adding Columns to a Normalized Table - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™
The following rules and restrictions apply to adding columns to a normalized table.
  • A table can have only one NORMALIZE column.
  • You can normalize an unnormalized table by altering its definition to specify the ADD normalize_option definition.
  • Adding a new column to a normalized table does not automatically add that column to the normalize_ignore_columns list.
  • Vantage renormalizes a normalized table if you add a column to the ignore column list.
  • When Vantage normalizes a previously unnormalized table, the value for the ignore columns is non-deterministic when multiple rows are normalized as one.
  • You can use an ALTER TABLE request to alter the normalize_ignore_columns column list by specifying an ADD normalize_ignore_columns option. This includes the complete normalize clause and the ignore columns.
  • You cannot add and drop normalization from a table within the same ALTER TABLE request.
The following rules and restrictions apply to adding a NORMALIZE option to an existing table.
  • The column you specify for the NORMALIZE option must have either a Period data type or a pseudo Period column.
  • You cannot specify a TRANSACTIONTIME column as a NORMALIZE column.
  • If you do not specify an explicit normalization condition, the default is ON MEETS OR OVERLAPS.
  • If an altered table that is to be normalized contains columns with a BLOB, CLOB, JSON, or XML data type, those columns must be specified in the normalize_ignore_column_name list for the NORMALIZE option.
  • Vantagee validates both UNIQUE and PRIMARY KEY constraints in the altered table with normalized rows. If a normalized row violates a UNIQUE or PRIMARY KEY constraint, the system returns an error to the requestor.
  • Vantage validates CHECK constraints for an altered table on a row inserted into a normalized table, and if the constraint is violated, the system returns an error to the requestor.

    This action prevents a security issue that can occur if a constraint is specified on the beginning or end of a normalized Period column. The input row violates the CHECK constraint but the normalized row does not. This situation cannot occur with UNIQUE constraints.

For information about dropping columns from a normalized table, see Dropping Columns from a Normalized Table.