Adding Columns to a Normalized Table
The following rules and restrictions apply to adding columns to a normalized table.
A table can have only one NORMALIZE column.
Attempting to add another NORMALIZE column aborts the request and returns an error
to the requestor.
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.
Teradata Database renormalizes a normalized table if you add a column to the ignore
When Teradata Database 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
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
If an altered table that is to be normalized contains columns with a BLOB, CLOB, JSON,
or XML data type, those columns must be explicitly specified in the normalize_ignore_column_name list for the NORMALIZE option. If a table contains such a column that is not included
in the normalize_ignore_column_name list, Teradata Database aborts the request and returns an error to the requestor.
Teradata Database 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,
Teradata Database aborts the request and returns an error to the requestor.
Teradata Database 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” on page 71.