If you specify CHECK as a composite table attribute, the syntax varies depending on whether the constraint is named or not.
- 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.
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.
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.
- 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)