CHECK Constraints - Teradata Vantage

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.

Vantage 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 Multilevel Partitioning and Single-Level Partitioning for more information about this.

The following rules apply to all CHECK constraints.

You can define CHECK constraints at column-level or at table-level.
The specified predicate for a CHECK constraint must be a simple boolean search condition.
Subqueries, aggregate expressions, and CASE expressions are not valid search conditions for CHECK constraint definitions.
You cannot specify CHECK constraints at any level for volatile tables or global temporary trace tables.
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.
Vantage tests CHECK constraints for character columns using the current session collation.
Therefore, a CHECK constraint may 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.
Vantage considers unnamed CHECK constraints specified with identical text and case to be duplicates, and returns an error when you submit such constraints 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 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 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.
If a row-level security-protected table is defined with one or more CHECK constraints, the enforcement of those constraints does not run any UDF security policies that are defined for the table. The enforcement of the CHECK constraint applies to the entire table. Therefore, CHECK constraints apply to all rows in a row-level security-protected 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 one unnamed distinct CHECK constraint for a column, Vantage combines the constraints into a single column-level constraint.

However, Vantage handles each named column-level CHECK constraint separately, as if it were a table-level named CHECK constraint.
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 typically references at least two 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.