这是本节的多页打印视图。点击此处打印.

支持的约束

1: 主键约束
2: 外键约束
3: 唯一约束
4: 检查约束
5: NOT NULL 约束

Vertica 支持标准 SQL 约束，如此部分中所述。

1 - 主键约束

主键包含一列或多列基元类型，其值可以唯一标识表行。一个表只能指定一个主键。您可以在创建表时或在现有表中使用 ALTER TABLE 标识表的主键。您不能将具有集合类型的列指定为键。

例如，下面的 CREATE TABLE 语句将 order_no 列定义为 store_orders 表的主键：

=> CREATE TABLE public.store_orders(
    order_no int PRIMARY KEY,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date,
    product_key int,
    product_version int
)
PARTITION BY ((date_part('year', order_date))::int);
CREATE TABLE

多列主键

一个主键可以包含多列。在这种情况下，CREATE TABLE 语句必须在定义所有列之后指定约束，如下所示：

=> CREATE TABLE public.product_dimension(
    product_key int,
    product_version int,
    product_description varchar(128),
    sku_number char(32) UNIQUE,
    category_description char(32),
    CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED
);
CREATE TABLE

或者，您可以使用单独的 ALTER TABLE...ADD CONSTRAINT 语句指定表的主键，如下所示：

=> ALTER TABLE product_dimension ADD CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED;
ALTER TABLE

强制执行主键

您可以通过强制执行主键约束来防止将重复值加载到主键中。这样做可以让您在主键和外键上联接表。当查询将维度表联接到事实表时，维度表中的每个主键都必须唯一地匹配事实表中的每个外键值。否则，尝试联接这些表会返回键强制执行错误。

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

提示

请考虑对主键列使用序列以保证唯一性，并避免主键约束可能产生的资源开销。

在主键上设置 NOT NULL

在定义主键时，Vertica 会自动将主键列设置为 NOT NULL。例如，当您按前面所示创建 product_dimension 表时，Vertica 会将主键列 product_key 和 product_version 设置为 NOT NULL，并将它们相应地存储在编录中：

> SELECT EXPORT_TABLES('','product_dimension');
...
CREATE TABLE public.product_dimension
(
    product_key int NOT NULL,
    product_version int NOT NULL,
    product_description varchar(128),
    sku_number char(32),
    category_description char(32),
    CONSTRAINT C_UNIQUE UNIQUE (sku_number) DISABLED,
    CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED
);

(1 row)

如果您使用 ALTER TABLE 为现有表指定主键，Vertica 会通知您将主键列设置为 NOT NULL：

WARNING 2623: Column "column-name" definition changed to NOT NULL

注意

如果删除主键约束，构成它的列仍设置为 NOT NULL。此约束只能通过 ALTER TABLE...ALTER COLUMN 来显式移除。

2 - 外键约束

外键通过引用其主键将一个表联接到另一个表。外键约束指定外键只能包含被引用主键中的值，从而确保在两个键上联接的数据的引用完整性。

您可以在创建表时标识表的外键，也可以在现有表中使用 ALTER TABLE 标识表的外键。例如，下面的 CREATE TABLE 语句定义了两个外键约束： fk_store_orders_store 和 fk_store_orders_vendor：

=> CREATE TABLE store.store_orders_fact(
    product_key int NOT NULL,
    product_version int NOT NULL,
    store_key int NOT NULL CONSTRAINT fk_store_orders_store REFERENCES store.store_dimension (store_key),
    vendor_key int NOT NULL CONSTRAINT fk_store_orders_vendor REFERENCES public.vendor_dimension (vendor_key),
    employee_key int NOT NULL,
    order_number int NOT NULL,
    date_ordered date,
    date_shipped date,
    expected_delivery_date date,
    date_delivered date,
    quantity_ordered int,
    quantity_delivered int,
    shipper_name varchar(32),
    unit_price int,
    shipping_cost int,
    total_order_cost int,
    quantity_in_stock int,
    reorder_level int,
    overstock_ceiling int
);

下面的 ALTER TABLE 语句在同一个表中添加外键约束 fk_store_orders_employee：

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
       FOREIGN KEY (employee_key) REFERENCES public.employee_dimension (employee_key);

REFERENCES 子句可以省略被引用列的名称，但前提是被引用列与外键列同名。例如，下面的 ALTER TABLE 语句等效于上面的语句：

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
       FOREIGN KEY (employee_key) REFERENCES public.employee_dimension;

多列外键

如果外键引用的主键包含多列，则外键必须包含相同数量的列。例如，public.product_dimension 表的主键包含两列：product_key 和 product_version。在这种情况下，CREATE TABLE 可以定义一个引用该主键的外键约束，如下所示：

=> CREATE TABLE store.store_orders_fact3(
    product_key int NOT NULL,
    product_version int NOT NULL,
    ...
   CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension (product_key, product_version)
);


CREATE TABLE

CREATE TABLE 只有在定义所有表列之后才能指定多列外键。还可以使用单独的 ALTER TABLE...ADD CONSTRAINT 语句指定表的外键：

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension (product_key, product_version);

在这两个示例中，约束都指定被引用表中的列。如果被引用列与外键列同名，则 REFERENCES 子句可以省略它们。例如，下面的 ALTER TABLE 语句等效于上面的语句：

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension;

外键中的 NULL 值

其列省略 NOT NULL 的外键可以包含 NULL 值，即使主键不包含 NULL 值也是如此。因此，即使不知道外键，也可以将行插入到表中。

3 - 唯一约束

您可以在列上指定唯一约束，以便该列中的每个值在所有其他值之间都是唯一的。您可以在创建表时定义唯一约束，也可以使用 ALTER TABLE 向现有表添加唯一约束。您不能对具有集合类型的列使用唯一性约束。

例如，下面的 ALTER TABLE 语句将 product_dimensions 表中的 sku_number 列定义为唯一列：

=> ALTER TABLE public.product_dimension ADD UNIQUE(sku_number);
WARNING 4887:  Table product_dimension has data. Queries using this table may give wrong results
if the data does not satisfy this constraint
HINT:  Use analyze_constraints() to check constraint violation on data

强制执行唯一约束

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

例如，前面的示例不在 sku_number 列中强制执行唯一约束。以下语句启用此约束：

=> ALTER TABLE public.product_dimension ALTER CONSTRAINT C_UNIQUE ENABLED;
ALTER TABLE

多列唯一约束

您可以定义由多个列组成的唯一约束。下面的 CREATE TABLE 语句指定每行中 c1 和 c2 列的组合值在所有其他行中必须唯一：

CREATE TABLE dim1 (c1 INTEGER,
    c2 INTEGER,
    c3 INTEGER,
  UNIQUE (c1, c2) ENABLED
);

4 - 检查约束

检查约束指定一个布尔表达式，它计算每一行的列值。如果给定行的表达式求值结果为 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。

5 - NOT NULL 约束

NOT NULL> 约束指定列不能包含 NULL 值。在进行所有表更新时，都必须在具有此约束的列中指定值。您可以在创建表时对列设置 NOT NULL 约束，也可以使用 ALTER TABLE 对现有表设置约束。

以下 CREATE TABLE 语句将三列定义为 NOT NULL。您不能在这些列中存储任何 NULL 值。

=> CREATE TABLE inventory ( date_key INTEGER NOT NULL,
                            product_key INTEGER NOT NULL,
                            warehouse_key INTEGER NOT NULL, ... );

以下 ALTER TABLE 语句将 product_dimensions 表中的 sku_number 列定义为 NOT NULL：

=> ALTER TABLE public.product_dimension ALTER COLUMN sku_number SET NOT NULL;
ALTER TABLE

强制执行 NOT NULL 约束

您不能对 NOT NULL 约束启用强制执行。您必须使用 ANALYZE_CONSTRAINTS 确定列数据是否包含 NULL 值，然后手动修复该函数发现的任何约束违规。

NOT NULL 和主键

在定义主键时，Vertica 会自动将主键列设置为 NOT NULL。如果删除主键约束，构成它的列仍设置为 NOT NULL。只能通过 ALTER TABLE...ALTER COLUMN 显式移除此约束。