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

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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.