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.
- Teradata Database renormalizes a normalized table if you add a column to the ignore column list.
- 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 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.
- 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 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.