Example: Adding a NORMALIZE Constraint to a Table
These examples show how to use an ALTER TABLE request to add a NORMALIZE constraint to a table. The system default condition for a NORMALIZE constraint is:
ON MEETS OR OVERLAPS
The examples all use the following table definition or an equivalent temporal table definition. The table definition does not specify a NORMALIZE constraint.
CREATE TABLE project ( emp_id INTEGER, project_name VARCHAR(20), dept_id INTEGER, duration PERIOD(DATE));
The following ALTER TABLE request modifies the project table to add a NORMALIZE constraint on the duration column. Rows are normalized only when the values of the emp_id, project_name, and dept_id columns are the same and the Period values for the duration column either meet or overlap.
ALTER TABLE project ADD NORMALIZE ON duration;
The following ALTER TABLE request modifies the project table to add a NORMALIZE constraint on the duration column. The dept_id column is the only column to ignore for normalization.
Rows are normalized only when the values of columns emp_id and project_name are the same and the Period values for the duration column meet or overlap.
ALTER TABLE project ADD NORMALIZE ALL BUT(dept_id) ON duration;
The following ALTER TABLE request modifies the project table to add a NORMALIZE constraint on the duration column that applies only when the Period values for the duration column overlap. The dept_id column is the only column to ignore for normalization.
Rows are normalized only when the values of columns emp_id and project_name are the same and the Period values for the duration column overlap.
ALTER TABLE project ADD NORMALIZE ALL BUT(dept_id) ON duration ON OVERLAPS;
The following ALTER TABLE request modifies the project table to add a NORMALIZE constraint on the duration column that applies only when the Period values for the duration column overlap. The dept_id column is the only column to ignore for normalization.
Rows are normalized only when the values of columns emp_id and project_name are the same and the Period values for the duration column overlap.
ALTER TABLE project ADD NORMALIZE ALL BUT(dept_id) ON duration ON OVERLAPS;
The next example defines the project table using a NORMALIZE condition of ON OVERLAPS on the duration column.
CREATE TABLE project ( emp_id INTEGER, project_name VARCHAR(20), dept_id INTEGER, duration PERIOD(DATE), NORMALIZE ALL BUT (dept_id) ON duration ON OVERLAPS);
Modify the definition of project to add the project_name column to the ALL BUT normalize_ignore column list and to add the condition OR MEETS to the existing ON OVERLAPS condition.
ALTER TABLE ADD NORMALIZE ALL BUT(dept_id,project_name) ON duration ON OVERLAPS OR MEETS;
Vantage renormalizes the project table after you submit this ALTER TABLE request.
Example: Dropping a NORMALIZE Constraint from a Table
This example uses a nontemporal version of the project table from Example: Adding a NORMALIZE Constraint to a Table except that this table definition specifies a NORMALIZE ALL BUT constraint with the dept_id column specified in the normalize_ignore column list and the NORMALIZE constraint is defined on the duration column.
CREATE TABLE project ( emp_id INTEGER, project_name VARCHAR(20), dept_id INTEGER, duration PERIOD(DATE), NORMALIZE ALL BUT(dept_id) ON duration);
The following ALTER TABLE request modifies the project table to remove the NORMALIZE constraint on the duration column.
ALTER TABLE project DROP NORMALIZE;