ALTER TABLE Examples | Teradata Vantage - ALTER TABLE NORMALIZE Examples - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;