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.