15.10 - CHECK (boolean_ condition) - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

An unnamed CHECK column constraint, where column_name is the left-hand side of boolean_condition.

The following rules apply only to column attribute CHECK constraints.

  • A column attribute CHECK constraint cannot reference other columns in the same table or in another table.
  • You can specify multiple CHECK constraints on a single column. Multiple unnamed column-level CHECK constraints are combined into a single column-level CHECK constraint. Multiple named column-level CHECK constraints are applied individually.

You can specify column-level CHECK constraints on nontemporal and temporal tables. For details about how temporal tables support CHECK constraints, see Temporal Table Support, B035-1182.

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

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

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

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

When you add or modify a CHECK constraint, Teradata Database scans all existing rows of the table to validate that the current values conform to the specified search condition. Otherwise, the system returns an error to the requestor and does not change the table definition.

CHECK constraints are valid for nontemporal and temporal tables. For details about how temporal tables support CHECK constraints, see Temporal Table Support, B035-1182.

This clause is a Teradata extension to the ANSI SQL:2011 standard.

A combination of table-level, column-level, and FOREIGN KEY … WITH CHECK OPTION constraints on a table used for a view can create a constraint expression that is too large to be parsed for INSERT and UPDATE requests.

CHECK constraints for character columns use the current session collation. A CHECK constraint may be satisfied for one session collation, but not met for another, even though the same data is inserted or updated.

You cannot specify CHECK constraints on columns that are defined with any of the following data types.

  • BLOB
  • CLOB
  • UDT
  • ARRAY/VARRAY
  • JSON
  • XML
  • Period
  • Geospatial

You cannot add CHECK constraints that reference BLOB or CLOB columns.

You cannot specify multiple unnamed CHECK constraints with identical boolean conditions. However, the following ALTER TABLE request is valid because the case of f1 and F1 is different:

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

The following request is not valid because the case of both f1 specifications is identical.

    ALTER TABLE t1 (
      ADD f1 INTEGER, CHECK (f1 > 0), CHECK (f1 > 0));

You can also 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.

The condition for a CHECK constraint can be any simple boolean search condition. Subqueries, aggregate expressions, and ordered analytic expressions are not valid in search conditions for CHECK constraint definitions.

You cannot specify CHECK constraints for:

  • volatile table columns
  • identity columns

You can use the following non-ANSI SQL constraint syntax for table attribute CHECK constraints.

    BETWEEN value_1 AND value_2

The system treats the constraint as if it were the following ANSI-compliant constraint.

    CHECK (column_name
 BETWEEN value_1
 AND value_2)

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

CONSTRAINT constraint_name
Named boolean conditional expression to restrict the values that can be inserted into, or updated for, a column.