CHECK constraints are the most general type of SQL constraint specification. Depending
on its position in the CREATE TABLE or ALTER TABLE SQL text, a CHECK constraint can
apply either to an individual column or to an entire table.
Teradata Database derives a table‑level partitioning CHECK constraint from the partitioning
expression for a partitioned table. The text for this derived constraint cannot exceed
16,000 characters. Otherwise, the system returns an error to the requestor. For more
information, see “Rules and Usage Notes for Partitioned Tables” on page 625.
The following rules apply to all CHECK constraints.
You can define CHECK constraints at either the column level or the table level.
You can define multiple CHECK constraints for a table.
You can define CHECK constraints at column-level or at table-level.
The specified predicate for a CHECK constraint can be any simple boolean search condition.
Subqueries, aggregate expressions, and CASE expressions are not valid search conditions
for CHECK constraint definitions.
You cannot invoke an SQL UDF from a CHECK constraint expression.
You cannot specify CHECK constraints at any level for volatile tables or global temporary
Note that a combination of table-level, column-level, and WITH CHECK OPTION on view
constraints can create a constraint expression that is too large to be parsed for
INSERT and UPDATE requests.
Teradata Database tests CHECK constraints for character columns using the current
As a result, a CHECK constraint might be met for one session collation, but violated
for another, even though the identical data is inserted or updated.
The following is an example of the potential importance of this. A CHECK constraint
is checked on insert and update of a base table character column, and might affect
whether a sparse join index defined with that character column is updated or not for
different session character collations, in which case different request results might
occur if the index is used in a query plan compared to the case where there is no
sparse join index to use.
Teradata Database considers unnamed CHECK constraints specified with identical text
and case to be duplicates, and returns an error when you submit them as part of a
CREATE TABLE or ALTER TABLE request.
For example, the following CREATE TABLE request is valid because the case of f1 and the case of F1 are different.
CREATE TABLE t1 (f1 INTEGER, CHECK (f1>0), CHECK (F1>0));
The following CREATE TABLE request, however, is not valid because the case of the 2 unnamed f1 constraints is identical. This request aborts and returns an error to the requestor.
CREATE TABLE t1 (f1 INTEGER, CHECK (f1>0), CHECK (f1>0));
The principal difference between defining a CHECK constraint at column‑level or at
table-level is that column-level constraints cannot reference other columns in their
table, while table-level constraints, by definition, must reference other columns in their table.
Columns defined with a data type from the following list cannot be a component of
a CHECK constraint.
You cannot define a CHECK constraint on a row‑level security constraint column of
a row‑level security‑protected table.
1If a row‑level security‑protected 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.
The following rules apply only to column-level CHECK constraints.
You can specify multiple column‑level CHECK constraints on a single column.
If you define more than 1 unnamed distinct CHECK constraint for a column, Teradata Database combines them into a single
However, Teradata Database handles each named column-level CHECK constraint separately, as if it were a table-level named CHECK
A column-level CHECK constraint cannot reference any other columns in its table.
The following rules apply only to table-level CHECK constraints.
A table-level constraint usually references at least 2 columns from its table.
Table-level CHECK constraint predicates cannot reference columns from other tables.
You can define a maximum of 100 table-level constraints for a table at one time.