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 column order_date.

• Ship5dAfterOrder specifies to check whether each ship_date value is no more than 5 days after order_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 OpenText™ Analytics Database 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)

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, the database 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, the database 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. the database 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))

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, the database determines if it is immutable. If it is not, the database 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.

For a usage example, see C++ example: calling a UDSF from a check constraint.