17.10 - Adding Columns to a Normalized Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
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. In this case 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.