17.10 - Example: Adding a NORMALIZE Constraint to a Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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.