检查约束

检查约束指定一个布尔表达式，它计算每一行的列值。如果给定行的表达式求值结果为 false，则该列值被视为违反约束。

例如，下表指定了两个命名检查约束：

IsYear2018 指定在 order_date 列中仅允许使用 2018 年的日期。
Ship5dAfterOrder 指定检查每个 ship_date 值是否在 order_date 之后不超过 5 天。

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)
);

当 Vertica 检查 store_orders_2018 中的数据是否出现约束违规时，它会对每一行的 order_date 和 ship_date 求值，以确定它们是否遵守各自的检查约束。

检查表达式

检查表达式只能引用当前表行，无法访问存储在其他表或数据库对象（如序列）中的数据，也无法访问其他表行中的数据。

检查约束表达式可以包括：

算法和串联字符串运算符
逻辑运算符，例如 AND、OR、 NOT
WHERE 介词，例如 CASE、IN、LIKE、BETWEEN、 IS [NOT] NULL
调用以下函数类型：
- 不可变的内置 SQL 函数，例如 LENGTH
- 不可变的 SQL 宏（请参阅下面的检查约束和 SQL 宏）
- 在组件工厂中标记为不可变的、由用户定义的标量函数（请参阅下面的检查约束和 UDSF）

示例检查表达式

以下检查表达式假设表包含所有被引用列，还假设这些列具有适当的数据类型：

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'))

检查表达式限制

检查表达式的求值结果必须为布尔值。但是，Vertica 不支持隐式转换为布尔值。例如，以下检查表达式无效：

CHECK (1)
CHECK ('hello')

检查表达式不能包含下列元素：

子查询 — 例如， CHECK (dept_id in (SELECT id FROM dept))
聚合 — 例如， CHECK (quantity < sum(quantity)/2)
窗口函数 — 例如， CHECK (RANK() over () < 3)
SQL 元函数 — 例如， CHECK (START_REFRESH('') = 0)
引用时期列
引用其他表或对象（如序列）或者系统上下文
调用在时间和空间上不可变的函数

强制执行检查约束

您可以使用配置参数 EnableNewCheckConstraintsByDefault 全局强制执行检查约束。还可以通过使用关键字 ENABLED 限定唯一约束来对特定表强制执行检查约束。在这两种情况下，Vertica 都会在将新值加载到表中时对检查约束求值，并在有任何约束违规时返回错误。或者，可以在更新表内容后使用 ANALYZE_CONSTRAINTS 来验证检查约束。有关详细信息，请参阅约束强制执行。

例如，可以使用 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

检查约束和 NULL

对于给定行，如果由于检查表达式中的某个列包含 NULL 而导致该表达式的求值结果未知，则该行将通过约束条件。Vertica 会对表达式进行求值，如果求值结果为 true 或未知，则它会认为该表达式符合条件。例如，如果 quantity 为 NULL，则 check (quantity > 0) 将通过验证。此结果不同于 WHERE 子句的工作原理。使用 WHERE 子句时，该行将不会包含在结果集中。

可以通过在检查约束表达式中显式包含 NULL 检查，在检查约束中禁止 NULL。例如： CHECK (quantity IS NOT NULL AND (quantity > 0))

提示

或者，在同一列上设置 NOT NULL 约束。

检查约束和 SQL 宏

如果 SQL 宏（用 SQL 编写的函数）不可变，检查约束可以调用该宏。不可变宏始终会为一组给定实参返回相同值。

当 DDL 语句在检查表达式中指定宏时，Vertica 会确定它是否不可变。如果不是，Vertica 会回退该 DDL 语句。

以下示例创建宏 mycompute，然后在检查表达式中使用它：

=> 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);

检查约束和 UDSF

检查约束可以调用用户定义的标量函数 (UDSF)。需要满足以下要求：

UDSF 必须在 UDx 工厂中标记为不可变。
约束正确处理 NULL 值。

当心

Vertica 会对加载或更新的每一行对已启用的检查约束进行求值。在大型表上调用计算量大的检查约束可能会产生相当大的系统开销。

有关使用示例，请参阅 C++ 示例：从检查约束调用 UDSF。