Check Constraints
A check constraint specifies a Boolean expression that evaluates a column's value on each row. If the expression resolves to false for a given row, the column value is regarded as violating the constraint.
For example, the following table specifies two named check constraints:
IsYear2018
specifies to allow only 2018 dates in columnorder_date
.Ship5dAfterOrder
specifies to check whether eachship_date
value is no more than 5 days afterorder_date
.
CREATE TABLE public.store_orders_2018 ( order_no int CONSTRAINT pk PRIMARY KEY, product_key int, product_version int, order_date timestamp NOT NULL, shipper varchar(20), ship_date date, CONSTRAINT IsYear2018 CHECK (DATE_PART('year', order_date)::int = 2018), CONSTRAINT Ship5dAfterOrder CHECK (DAYOFYEAR(ship_date) - DAYOFYEAR(order_date) <=5) );
When Vertica checks the data in store_orders_2018
for constraint violations, it evaluates the values of order_date
and ship_date
on each row, to determine whether they comply with their respective check constraints.
Check Expressions
A check expression can only reference the current table row; it cannot access data stored in other tables or database objects, such as sequences. It also cannot access data in other table rows.
A check constraint expression can include:
- Arithmetic and concatenated string operators
- Logical operators such as
AND
,OR
,NOT
- WHERE prepositions such as
CASE
,IN
,LIKE
,BETWEEN
,IS [NOT] NULL
-
Calls to the following function types:
- Immutable built-in SQL functions such as
LENGTH
- Immutable SQL macros (see Check Constraints and SQL Macros below)
- User-defined scalar functions that are marked as immutable in the component factory (see Check Constraints and UDSFs below)
- Immutable built-in SQL functions such as
Example Check Expressions
The following check expressions assume that the table contains all referenced columns and that they have the appropriate data types:
CONSTRAINT chk_pos_quant CHECK (quantity > 0)
CONSTRAINT chk_pqe CHECK (price*quantity = extended_price)
CONSTRAINT size_sml CHECK (size in ('s', 'm', 'l', 'xl'))
CHECK (
regexp_like(dept_name, '^[a-z]+$', 'i') OR (dept_name = 'inside sales'))
Check Expression Restrictions
A check expression must evaluate to a Boolean value. However, Vertica does not support implicit conversions to Boolean values. For example, the following check expressions are invalid:
CHECK (1)
CHECK ('hello')
A check expression cannot include the following elements:
- Subqueries—for example,
CHECK (dept_id in (SELECT id FROM dept))
- Aggregates—for example,
CHECK (quantity < sum(quantity)/2)
- Window functions—for example,
CHECK (RANK() over () < 3)
- SQL meta-functions—for example,
CHECK (START_REFRESH('') = 0)
- References to the epoch column
- References to other tables or objects (for example, sequences), or system context
- Invocation of functions that are not immutable in time and space
Enforcing Check Constraints
You can enforce check constraints globally with configuration parameter EnableNewCheckConstraintsByDefault
. You an also enforce check constraints for specific tables by qualifying unique constraints with the keyword ENABLED
. In both cases, Vertica evaluates check constraints as new values are loaded into the table, and returns with errors on any constraint violations. Alternatively, you can use ANALYZE_CONSTRAINTS
to validate check constraints after updating the table contents. For details, see Constraint Enforcement.
For example, you can enable the constraints shown earlier with ALTER TABLE…ALTER CONSTRAINT
:
=> ALTER TABLE store_orders_2018 ALTER CONSTRAINT IsYear2018 ENABLED; ALTER TABLE => ALTER TABLE store_orders_2018 ALTER CONSTRAINT Ship5dAfterOrder ENABLED; ALTER TABLE
Check Constraints and Nulls
If a check expression evaluates to unknown for a given row because a column within the expression contains a null, the row passes the constraint condition. Vertica evaluates the expression 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 works. 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))
Alternatively, set a NOT NULL
constraint on the same column.
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 a DDL statement specifies a macro in a check expression, Vertica determines if it is immutable. If it is not, Vertica rolls back the statement.
The following example creates the macro mycompute
and then uses it in a check 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 UDSFs
A check constraint can call user-defined scalar functions (UDSFs). The following requirements apply:
- The UDSF must be marked as immutable in the UDx factory.
- The constraint handles null values properly.
Vertica evaluates an enabled check constraint on every row that is loaded or updated. Invoking a computationally expensive check constraint on a large table is liable to incur considerable system overhead.
For a usage example, see C++ Example: Calling a UDSF from a Check Constraint in Extending Vertica.