17.05 - CHECK (boolean_condition) - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

If you specify CHECK as a composite table attribute, the syntax varies depending on whether the constraint is named or not.

The following rules apply only to table attribute CHECK constraints:
  • A table-level CHECK constraint must reference at least 1 column from its table.
  • A maximum of 100 table-level constraints can be defined for a given table.

A table attribute CHECK constraint can compare any columns defined for its table, both against each other and against constants.

For an unnamed CHECK column constraint, use this syntax:
     CHECK (boolean_condition)

You can specify CHECK constraints as column attributes or as table attributes.

You cannot include subqueries, aggregate, or ordered analytic functions in a CHECK constraint.

You cannot specify CHECK constraints for identity columns or for the columns of a volatile table.

You can create a constraint expression that is too large to be parsed for INSERT and UPDATE requests by specifying a combination of table-level, column-level, and FOREIGN KEY … WITH CHECK OPTION constraints on a table that underlies a view.
You cannot specify CHECK constraints on columns with the following data types:
  • BLOB
  • CLOB
  • UDT
  • ARRAY/VARRAY
  • Period
  • XML
  • Geospatial
  • JSON
  • DATASET

Vantage tests CHECK constraints for character columns using the current session collation. As a result, a CHECK constraint might be met for one session collation, but not met for another even though the identical data is inserted or updated for both.

Unnamed CHECK constraints with identical boolean conditions and case are considered duplicates, and the system returns an error when you specify them.

For example, the following CREATE TABLE request is valid because the case of f1 and F1 is different:

    CREATE TABLE t1 (
      f1 INTEGER, CHECK (f1 > 0), CHECK (F1 > 0));

The following CREATE TABLE request is not valid because the case is the same for both f1 specifications.

    CREATE TABLE t1 (
      f1 INTEGER, CHECK (f1 > 0), CHECK (f1 > 0));

For column attribute and table attribute CHECK constraints, you can specify any simple boolean search condition. CHECK constraint definitions cannot include subqueries, aggregate expressions, or ordered analytic expressions.

You cannot specify CHECK constraints for:
  • Volatile table columns.
  • Identity columns.

The search condition for a CHECK constraint cannot specify SET operators.

Vantage supports the following non-ANSI SQL constraint syntax for table attribute CHECK constraints.

    BETWEEN value_1
 AND value_2

The system processes the syntax like the following ANSI-compliant constraint.

    CHECK (column_name
 BETWEEN value_1
 AND value_2)

You can use the BETWEEN … AND operator as a form of CHECK constraint except for volatile table columns, identity columns, UDT columns, ARRAY, VARRAY, Geospatial columns, Period columns, BLOB columns, or CLOB columns.

For information about using CHECK constraints to construct domains, see Teradata Vantage™ - Database Design, B035-1094.

For a normalized table, Vantage validates CHECK constraints on the input row or new row.

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

If a row-level security table is defined with 1 or more CHECK constraints, the enforcement of those constraints does not execute any UDF security policies that are defined for the table. The enforcement of the CHECK constraint applies to the entire table. This means that CHECK constraints apply to all of the rows in a table, not just to the rows that are user-visible.

CONSTRAINT constraint_name
An optionally named simple boolean conditional expression used to constrain the values that can be inserted into, or updated for, a column. For a named CHECK column constraint, use this syntax:
     CONSTRAINT  constraint_name  CHECK (boolean_condition)