CHECK constraints are the most general type of SQL constraint specifications. Depending on its position in the CREATE or ALTER TABLE SQL text, a CHECK constraint can apply either to an individual column or to multiple columns.
Teradata Database derives a table‑level index partitioning CHECK constraint from the partitioning expression for a PPI table. The text for this derived constraint cannot exceed 16,000 characters; otherwise, the system aborts the request and returns an error to the requestor. See “Partitioning CHECK Constraints” on page 376 and “Partitioning CHECK Constraints for Single‑Level Partitioning” on page 362 for more information about this.
The following rules apply to all CHECK constraints.
Subqueries, aggregate expressions, and CASE expressions are not valid search conditions for CHECK constraint definitions.
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 gets 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.
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 two 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 following rules apply only to column-level CHECK constraints.
If you define more than one unnamed distinct CHECK constraint for a column, Teradata Database combines them into a single column‑level constraint.
However, Teradata Database handles each named column-level CHECK constraint separately, as if it were a table-level named CHECK constraint.
The following rules apply only to table-level CHECK constraints.