Check Constraints

A check constraint specifies a SQL predicate (Boolean expression) that is evaluated independently on each row of a table. The predicate cannot access data stored in other tables or database objects, such as sequences. It also cannot access data in other rows of the current table.

As with other constraints, check constraints help ensure data integrity. By default, Vertica automatically enforces check constraints. You have the option of turning this feature off for specific constraints when you create or alter them. You can also set the configuration parameter EnableNewCheckConstraintsByDefault to 0 (disabled), which automatically disables all check constraints that you subsequently create or alter. You can override this parameter by explicitly enabling a specific constraint.

For more information on enabling and disabling constraints, refer to Enforcing Primary Key, Unique Key, and Check Constraints Automatically.

The following examples show how you can use check constraints:

Add a constraint named smplcheck to the column n1without specifically enabling or disabling the constraint:

=> CREATE TABLE checksample (n1 int CONSTRAINT smplcheck CHECK(n1<50),n2 int, n3 int);

Add a constraint in the disabled state:

=> CREATE TABLE checksample2 (n1 int CONSTRAINT smplcheck2 CHECK(n1<50) DISABLED,n2 int, n3 int);

Use ALTER TABLE to add a constraint to an existing table:

=> ALTER TABLE checksample ADD CONSTRAINT smplcheck2 CHECK(n2<100);

Use ALTER TABLE to disable an existing constraint:

=> ALTER TABLE checksample ALTER CONSTRAINT smplcheck2 DISABLED;

When you enable automatic enforcement of check constraints, Vertica applies enforcement for:

Alternatively, rather than automatic enforcement, you can use ANALYZE_CONSTRAINTS to validate constraints after issuing these statements. ANALYZE_CONSTRAINTS reports all constraint violations. For more information on enabling and disabling check constraints, refer to Enforcing Primary Key, Unique Key, and Check Constraints Automatically

Allowed Syntax for Check Constraint Predicates

A check constraint predicate can include:

The following examples show you how you can define hypothetical check constraints on a table. The examples assume that the table contains the referenced attributes (column names), defined as appropriate types. (The last one is an unnamed check constraint.)

CONSTRAINT chk_pos_quant CHECK (quantity > 0)
CONSTRAINT chk_pqe CHECK (price*quantity = extended_price)
CONSTRAINT size_sml CHECK (size in (‘small’, ‘medium’, ‘large’, ‘x- large’))
CHECK (regexp_like(dept_name, ‘^[a-z]+$’, ‘i’) OR (dept_name = ‘inside sales’))

Restrictions on Check Constraints

A check constraint expression must evaluate to a Boolean value. However, Vertica does not support implicit conversion to a Boolean value. For example, Vertica would return an error for the following (invalid) check constraint:

CHECK (1) -- produces an error
CHECK ('hello') -- produces an error

You must always enclose a check constraint in parentheses:

check (quantity > 0)

A check constraint expression cannot include any of the following elements:

Check Constraints and Nulls

If a check constraint expression evaluates to an unknown for a given row because a column within the expression contains a null, the row passes the constraint condition.  Vertica evaluates the predicate and considers it satisfied if it resolves to either true or unknown. For example, check (quantity > 0) passes validation if quantity is null. This result differs from how a WHERE clause would work. With a WHERE clause, the row would not be included in the result set.

You can prohibit nulls in a check constraint by explicitly including a null check in the check constraint expression. For example, CHECK (quantity IS NOT NULL AND (quantity > 0)). You could alternatively include a not null constraint separate from the check constraint.

Check Constraints and SQL Macros

A check constraint can call a SQL macro (a function written in SQL) if the macro is immutable. An immutable macro always returns the same value for a given set of arguments.

When you include a macro, Vertica determines if it is immutable. If it is not, Vertica rolls back the DDL statement.

This example shows you can create a macro name, mycompute, and then use it within a check constraint expression:

=> CREATE OR REPLACE FUNCTION mycompute(j int, name1 varchar)
RETURN int AS BEGIN RETURN (j + length(name1)); END;
=> ALTER TABLE sampletable
ADD CONSTRAINT chk_compute
CHECK(mycompute(weekly_hours, name1))<50);

Check Constraints and User-Defined Extensions (UDxs)

A check constraint can call user-defined scalar function (UDSFs), but not other kinds of UDxs. To use a UDSF, you must first mark it as immutable in the UDx factory.

If you use a UDSF within a check constraint, you must verify that the immutable tag on the referenced functions is correct and that the constraint handles null values properly. Otherwise, the check constraint may not work as you intended. In addition, Vertica evaluates the predicate of an enabled check constraint on every row that is loaded or updated. For performance reasons, you may want to avoid invoking a computationally expensive check constraint.

To view a sample UDSF that you can use within a check constraint, refer to C++ Example: Calling a UDSF from a Check Constraint.

Dropping a Check Constraint

Use ALTER TABLE with the DROP CONSTRAINT option to explicitly drop a constraint.

If you drop a table the includes check constraints, Vertica automatically deletes all check constraints associated with the table. The CASCADE option is not needed.

If you drop a table column (or function object):

If you rename a table column, Vertica updates the constraint predicate to use the new name.