15.00 - Semantic Constraint Specifications - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Semantic Constraint Specifications

A name and a data type must be specified for each column defined for a table. Each specified column can be further defined with one or more attribute (see SQL Data Types and Literals) or constraint definitions.

There are several different specifications for constraints, some of which apply to multiple categories of constraints.

You cannot declare semantic database constraints on columns defined with XML, BLOB, CLOB, BLOB‑based UDT, CLOB‑based UDT, XML‑based UDT, ARRAY/VARRAY, Period, JSON, or Geospatial data types.

The following constraints are SQL column definition attributes that specify column-level integrity constraints:

  • UNIQUE constraint definition on a single column.
  • UNIQUE constraints are implemented as USIs.

    If a row‑level security‑protected table is defined with a UNIQUE constraint, enforcement of the UNIQUE constraint does not execute any row-level security policy defined for the table.

    UNIQUE constraints are applicable to all rows in a row‑level security‑protected table, not just to user‑visible rows.

    You can specify UNIQUE constraints on columns having a UDT data type as long as the UDT is not based on XML, BLOB, or CLOB data.

    You cannot specify UNIQUE constraints on columns having any of the following data types:

  • XML
  • BLOB
  • CLOB
  • XML‑based UDT
  • BLOB‑based UDT
  • CLOB‑based UDT
  • ARRAY
  • VARRAY
  • Period
  • Geospatial
  • JSON
  • You cannot define a UNIQUE constraint on a row‑level security constraint column of a row‑level security‑protected table.

    If you do not specify either an explicit PRIMARY INDEX or NO PRIMARY INDEX, Teradata Database converts any UNIQUE constraints you define to either a unique primary index or a unique secondary index, depending on whether a primary key is also defined for the table (see “Primary Index Defaults” on page 263 for details).

     

    IF PrimaryIndexDefault is set to D or P and a CREATE TABLE request specifies this constraint on a column set without also specifying either a PRIMARY INDEX or NO PRIMARY INDEX option …

    THEN Teradata Database converts the …

    PRIMARY INDEX

    column set defined as the primary key to the unique primary index for the table.

    Any additional column sets defined with UNIQUE constraints are redefined as unique secondary indexes.

    PRIMARY INDEX and UNIQUE constraints are implemented as unique secondary indexes. They are also explicitly redefined as unique secondary indexes in the SQL create text for the table definition.

    UNIQUE

    first column set defined with a UNIQUE constraint to the unique primary index for the table.

    Any other column sets defined with UNIQUE constraints are redefined as either unique secondary indexes.

    The primary index defaults described in the preceding table do not apply to column‑partitioned tables, where the default is always NO PRIMARY INDEX regardless of the setting of the DBS Control parameter PrimaryIndexDefault.

  • CHECK constraint definition on a single column.
  • CHECK constraints are not implemented as indexes.

    If a row‑level security‑protected table is defined with a CHECK constraint, enforcement of the constraint does not execute any security policy defined for the table.

    CHECK constraints are applicable to all rows in a row‑level security‑protected table, not just to user‑visible rows.

    You cannot define a CHECK constraint on a row‑level security constraint column of a row‑level security‑protected table.

    You cannot specify CHECK constraints on columns having any of the following data types:

  • XML
  • BLOB
  • CLOB
  • XML‑based UDT
  • BLOB‑based UDT
  • CLOB‑based UDT
  • ARRAY/VARRAY
  • UDT
  • Period
  • Geospatial
  • JSON
  • PRIMARY KEY constraint definition on a single column.
  • PRIMARY KEY constraints are implemented as USIs.

    If a PRIMARY KEY constraint is defined where either or both the parent and child table are row‑level security‑protected, execution of the referential integrity constraint does not execute any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row‑level security‑protected.

    You can specify PRIMARY KEY constraints on columns having a UDT data type.

    You cannot specify PRIMARY KEY constraints on columns having any of the following data types:

  • XML
  • BLOB
  • CLOB
  • XML‑based UDT
  • BLOB‑based UDT
  • CLOB‑based UDT
  • ARRAY/VARRAY
  • Period
  • Geospatial
  • JSON
  • You cannot define a PRIMARY KEY constraint on a row‑level security‑protected column.

    If you do not specify an explicit PRIMARY INDEX or NO PRIMARY INDEX option, Teradata Database converts any PRIMARY KEY constraint you define for a table to a unique primary index (see “Primary Index Defaults” on page 263 for complete details).

  • REFERENCES constraint definition on a single column.
  • REFERENCES constraints are not implemented as indexes.

    If a REFERENCES constraint is defined where either or both the parent and child table are row‑level security‑protected, execution of the referential integrity constraint does not execute any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row‑level security‑protected.

    You cannot specify foreign key REFERENCES constraints on columns having any of the following data types.

  • XML
  • BLOB
  • CLOB
  • XML‑based UDT
  • BLOB‑based UDT
  • CLOB‑based UDT
  • UDT
  • ARRAY/VARRAY
  • Period
  • Geospatial
  • JSON
  • Temporal tables do not support foreign key REFERENCES constraints for standard or batch referential integrity.

    See ANSI Temporal Table Support and Temporal Table Support for details.

    The following constraints are SQL table definition attributes that specify table‑level and intertable integrity constraints:

  • CHECK constraint definition on a composite column set.
  • FOREIGN KEY … REFERENCES constraint definition on a composite column set.
  • PRIMARY KEY constraint definition on a composite column set.
  • UNIQUE constraint definition on a composite column set.
  • You cannot specify constraints other than NULL or NOT NULL for global temporary trace tables. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language.