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

约束

1: 支持的约束

1.1: 主键约束
1.2: 外键约束
1.3: 唯一约束
1.4: 检查约束
1.5: NOT NULL 约束

2: 设置约束
3: 删除约束
4: 对约束进行命名
5: 检测约束违规
6: 约束强制执行

6.1: 约束强制执行级别
6.2: 验证约束强制执行
6.3: 报告约束违规
6.4: 约束强制执行和锁定
6.5: 约束强制执行和性能
6.6: 强制执行的约束的投影
6.7: 约束强制执行限制

约束将设置表格列中允许哪些数据的相关规则。使用约束有助于维护数据完整性。例如，您可以将列限制为仅允许唯一值或禁止 NULL 值。主键等约束也有助于优化器生成查询计划，促进数据访问更快捷，特别是对于联接更是如此。

您可分别使用 CREATE TABLE 和 ALTER TABLE...ADD CONSTRAINT 对新表和现有表设置约束。

1 - 支持的约束

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

1.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 来显式移除。

1.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 值也是如此。因此，即使不知道外键，也可以将行插入到表中。

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

1.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。

1.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 显式移除此约束。

2 - 设置约束

您可以使用 CREATE TABLE 和 分别对新表和现有表设置约束ALTER TABLE...ADD CONSTRAINT。

对新表设置约束

CREATE TABLE 可以通过两种方式指定约束：作为列定义的一部分，或遵循所有列定义。

例如，以下 CREATE TABLE 语句对 sku_number、NOT NULL 和 UNIQUE 列设置两个约束。在定义了所有列之后，该语句还设置一个由以下两列组成的主键：product_key 和 product_version：

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

对现有表设置约束

ALTER TABLE...ADD CONSTRAINT 向现有表添加约束。例如，以下语句为 product_version 列指定唯一值：

=> ALTER TABLE prod_dimension ADD CONSTRAINT u_product_versions UNIQUE (product_version) ENABLED;
ALTER TABLE

验证现有数据

在已包含数据的列上添加约束时，如果以下条件都成立，Vertica 会立即验证列值：

约束是主键、唯一或检查约束。
约束是强制执行的。

如果其中任何一个条件不成立，Vertica 不会验证列值。在这种情况下，您必须调用 ANALYZE_CONSTRAINTS 来查找约束违规。否则，查询可能会返回意外结果。有关详细信息，请参阅检测约束违规。

导出表约束

无论您是在列定义中还是在表中指定约束，Vertica 都会将表 DDL 存储为 CREATE 语句的一部分，并将约束导出。有一个例外：外键作为 ALTER TABLE 语句存储和导出。

例如：

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

3 - 删除约束

ALTER TABLE 通过两种方式从表中删除约束：

ALTER TABLE...DROP CONSTRAINT 移除命名表的约束。
ALTER TABLE...ALTER COLUMN 移除列的 NOT NULL 约束。

例如，表 store_orders_2018 指定以下约束：

命名约束 pk 将列 order_no 标识为主键。
命名约束 IsYear2018 指定的检查约束在 order_date 列中仅允许使用 2018 年的日期。
命名约束 Ship5dAfterOrder 指定的检查约束禁止使用 order_date 之后 5 天以上的任何 ship_date 值。
order_no 和 order_date 列设置为 NOT NULL。

CREATE TABLE public.store_orders_2018 (
    order_no int NOT NULL 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)
);

删除命名约束

您可以使用 ALTER TABLE...DROP CONSTRAINT 移除主键、外键、检查和唯一约束，这需要您提供它们的名称。例如，您按如下方式移除表 store_orders_2018 中的主键约束：

=> ALTER TABLE store_orders_2018 DROP CONSTRAINT pk;
ALTER TABLE
=> SELECT export_tables('','store_orders_2018');
                                                             export_tables
---------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE public.store_orders_2018
(
    order_no int NOT NULL,
    product_key int,
    product_version int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date,
    CONSTRAINT IsYear2018 CHECK (((date_part('year', store_orders_2018.order_date))::int = 2018)) ENABLED,
    CONSTRAINT Ship5dAfterOrder CHECK (((dayofyear(store_orders_2018.ship_date) - dayofyear(store_orders_2018.order_date)) <= 5)) ENABLED
);

重要

如果您没有对约束进行显式命名，Vertica 会分配自己的名称。您可以使用 EXPORT_TABLES 或通过查询以下系统表来从 Vertica 编录中获取所有约束名称：

删除 NOT NULL 约束

使用 ALTER TABLE...ALTER COLUMN 删除列的 NOT NULL 约束，如以下示例所示：

=> ALTER TABLE store_orders_2018 ALTER COLUMN order_date DROP NOT NULL;
ALTER TABLE

删除主键

如果某个主键约束被另一个表中的外键约束引用，则不能删除该主键约束。若要删除主键，必须首先删除所有引用它的外键。

删除由约束引用的列

如果您尝试删除由同一表中的约束引用的列，则删除操作会返回错误。例如，检查约束 Ship5dAfterOrder 会引用两列：order_date 和 ship_date。如果您尝试删除其中的任一列，Vertica 会返回以下错误消息：

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date;
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:
Constraint Ship5dAfterOrder references column ship_date
HINT:  Use DROP .. CASCADE to drop or modify the dependent objects

在这种情况下，您必须使用 CASCADE 选项限定 DROP COLUMN 子句，该选项指定要删除列及其依赖对象 — 在这种情况下，约束 Ship5dAfterOrder：

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date CASCADE;
ALTER TABLE

调用 Vertica 函数 EXPORT_TABLES 可确认列和约束是否均已移除：

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date CASCADE;
ALTER TABLE
dbadmin=> SELECT export_tables('','store_orders_2018');
                                              export_tables
---------------------------------------------------------------------------------------------------------

CREATE TABLE public.store_orders_2018
(
    order_no int NOT NULL,
    product_key int,
    product_version int,
    order_date timestamp,
    shipper varchar(20),
    CONSTRAINT IsYear2018 CHECK (((date_part('year', store_orders_2018.order_date))::int = 2018)) ENABLED
);

(1 row)

4 - 对约束进行命名

必须对以下约束进行命名。

PRIMARY KEY
REFERENCES （外键）
CHECK
UNIQUE

在定义这些约束时对它们进行命名。如果您未分配名称，Vertica 会自动分配名称。

用户分配的约束名称

当您使用 CREATE TABLE 或 ALTER TABLE...ADD CONSTRAINT 定义约束时，您可以为约束分配名称。例如，以下 CREATE TABLE 语句分别对主键约束和检查约束 pk 和 date_c 进行命名：

=> CREATE TABLE public.store_orders_2016
(
    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 date_c CHECK (date_part('year', order_date)::int = 2016)
)
PARTITION BY ((date_part('year', order_date))::int);
CREATE TABLE

以下 ALTER TABLE 语句添加外键约束 fk：

=> ALTER TABLE public.store_orders_2016 ADD CONSTRAINT fk
    FOREIGN KEY (product_key, product_version)
    REFERENCES public.product_dimension (product_key, product_version);

自动分配的约束名称

对约束进行命名是可选的。如果您不为约束分配名称，Vertica 将使用以下约定分配自己的名称：

C_constraint-type[_integer]

例如，下表定义了两个名为 a 和 b 列，并将它们限制为仅包含唯一值：

=> CREATE TABLE t1 (a int UNIQUE, b int UNIQUE );
CREATE TABLE

当您使用 EXPORT_TABLES 导出表的 DDL 时，该函数的输出中显示 Vertica 已为 a 列分配约束名称 C_UNIQUE，已为 b 列分配约束名称 C_UNIQUE_1：


=> SELECT EXPORT_TABLES('','t1');
CREATE TABLE public.t1
(
    a int,
    b int,
    CONSTRAINT C_UNIQUE UNIQUE (a) DISABLED,
    CONSTRAINT C_UNIQUE_1 UNIQUE (b) DISABLED
);

(1 row)

查看约束名称

您可以通过使用 EXPORT_TABLES 导出表的 DDL 来查看表约束的名称，如前面所示。您还可以查询以下系统表：

例如，以下查询获取 online_sales 架构中所有主键约束和外键约束的名称：

=> SELECT table_name, constraint_name, column_name, constraint_type FROM constraint_columns
     WHERE constraint_type in ('p','f') AND table_schema='online_sales'
     ORDER BY table_name, constraint_type, constraint_name;
      table_name       |      constraint_name      |   column_name   | constraint_type
-----------------------+---------------------------+-----------------+-----------------
 call_center_dimension | C_PRIMARY                 | call_center_key | p
 online_page_dimension | C_PRIMARY                 | online_page_key | p
 online_sales_fact     | fk_online_sales_cc        | call_center_key | f
 online_sales_fact     | fk_online_sales_customer  | customer_key    | f
 online_sales_fact     | fk_online_sales_op        | online_page_key | f
 online_sales_fact     | fk_online_sales_product   | product_version | f
 online_sales_fact     | fk_online_sales_product   | product_key     | f
 online_sales_fact     | fk_online_sales_promotion | promotion_key   | f
 online_sales_fact     | fk_online_sales_saledate  | sale_date_key   | f
 online_sales_fact     | fk_online_sales_shipdate  | ship_date_key   | f
 online_sales_fact     | fk_online_sales_shipping  | shipping_key    | f
 online_sales_fact     | fk_online_sales_warehouse | warehouse_key   | f
(12 rows)

使用约束名称

您必须引用约束名称才能执行以下任务：

启用或禁用约束强制执行。
删除约束。

例如，以下 ALTER TABLE 语句在 store_orders_2016 表中启用对 pk 约束的强制执行：


=> ALTER TABLE public.store_orders_2016 ALTER CONSTRAINT pk ENABLED;
ALTER TABLE

以下语句删除同一个表中的另一个约束：

=> ALTER TABLE public.store_orders_2016 DROP CONSTRAINT date_c;
ALTER TABLE

5 - 检测约束违规

ANALYZE_CONSTRAINTS 分析和报告给定架构中的表约束违规。您可以使用 ANALYZE_CONSTRAINTS 来分析单个表、表中的特定列或架构中的所有表。您通常针对未强制执行主键、唯一性或检查约束的表使用此函数。您还可以使用 ANALYZE_CONSTRAINTS 来检查外键的引用完整性。

在最简单的用例中，ANALYZE_CONSTRAINTS 是包含两个步骤的过程：

针对所需的表运行 ANALYZE_CONSTRAINTS。 ANALYZE_CONSTRAINTS 报告所有约束违规。
使用报告来修复违规。

您还可以在以下情况下使用 ANALYZE_CONSTRAINTS：

分析已强制执行约束的表。
检测由 COPY 操作引入的约束违规，并在提交复制事务之前解决它们。

分析已强制执行约束的表

如果对表强制执行约束，而且 DML 操作返回约束违规，则 Vertica 会在回退操作之前报告有限数量的约束违规。当您尝试加载大量包含多个约束违规（例如，键值重复）的数据时，这可能会出现问题。在这种情况下，按如下方式使用 ANALYZE_CONSTRAINTS：

针对目标表暂时禁用所有约束的强制执行。
运行 DML 操作。
操作返回后，对表运行 ANALYZE_CONSTRAINTS。 ANALYZE_CONSTRAINTS 报告所有约束违规。
使用报告来修复违规。
对表重新启用约束强制执行。

在 COPY 事务中使用 ANALYZE_CONSTRAINTS

使用 ANALYZE_CONSTRAINTS 来检测和解决由 COPY 操作引入的约束违规，如下所示：

使用 COPY...NO COMMIT 将源数据复制到目标表中。
调用 ANALYZE_CONSTRAINTS 以检查目标表及其未提交的更新。
如果 ANALYZE_CONSTRAINTS 报告约束违规，则回退复制事务。
使用报告修复违规，然后重新执行复制操作。

有关使用 COPY...NO COMMIT 的详细信息，请参阅使用事务暂存加载。

分布约束分析

ANALYZE_CONSTRAINTS 作为原子操作运行 — 也就是说，它在计算指定范围内的所有约束之后才会返回。例如，如果您对表运行 ANALYZE_CONSTRAINTS，则该函数仅在针对列数据计算所有列约束后才返回。如果表中包含大量带有约束的列，并且包含非常大的数据集，则 ANALYZE_CONSTRAINTS 可能会耗尽所有可用内存并返回内存不足错误。同时对多个表或整个数据库运行 ANALYZE_CONSTRAINTS 会增加这种风险。

您可以通过将配置参数 MaxConstraintChecksPerQuery 设置为正整数（默认值为 ‑1）来最大程度地降低内存不足错误所带来的风险。例如，如果此参数设置为 20，并且您对包含 38 个列约束的表运行 ANALYZE_CONSTRAINTS，则该函数会将其工作分成两个单独的查询。 ANALYZE_CONSTRAINTS 创建一个临时表来加载和编译来自两个查询的结果，然后返回复合结果集。

MaxConstraintChecksPerQuery 只能在数据库级别进行设置，并且会产生一定的开销。设置后，提交到由 ANALYZE_CONSTRAINTS 创建的临时表会导致所有挂起的数据库事务自动提交。将此参数设置为合理的数值（例如 20）应该可以最大限度地减少对性能的影响。

6 - 约束强制执行

您可以强制执行以下约束：

PRIMARY KEY
UNIQUE
CHECK

对表启用约束强制执行之后，Vertica 会立即将该约束应用到表的当前内容，以及稍后添加或更新的所有内容。

用于调用约束强制执行的操作

以下 DDL 和 DML 操作可调用约束强制执行：

ALTER TABLE...ADD CONSTRAINT 和 ALTER TABLE...ALTER CONSTRAINT
INSERT 和 INSERT...SELECT
COPY
UPDATE
MERGE
分区函数：
- COPY_PARTITIONS_TO_TABLE
- MOVE_PARTITIONS_TO_TABLE
- SWAP_PARTITIONS_BETWEEN_TABLES

益处和代价

启用约束强制执行有助于最大程度地减少加载后的维护任务，例如：使用 ANALYZE_CONSTRAINTS 单独验证数据，然后处理其返回的约束违规问题。

强制执行键约束，尤其是启用主键约束，可以帮助优化器更快地生成查询计划，特别是联接的查询计划。当对表实施主键约束时，优化器假定该表中的所有行均未包含重复的键值。

在某些情况下，大量的约束强制执行（尤其是在大型数据表中）可能会导致系统开销显著增加。有关详细信息，请参阅约束强制执行和性能。

6.1 - 约束强制执行级别

可以在两个级别强制执行约束：

数据库配置参数
[表约束](#Table Co)

约束强制执行参数

Vertica 支持通过以下三个布尔参数来强制执行约束：

表约束强制执行

可以通过使用关键字 ENABLED 或 DISABLED 限定约束，借助于 CREATE TABLE 和 ALTER TABLE 对表设置约束强制执行。以下 CREATE TABLE 语句在其 order_qty 列的定义中对检查约束启用强制执行：

=> CREATE TABLE new_orders (
   cust_id int,
   order_date timestamp DEFAULT CURRENT_TIMESTAMP,
   product_id varchar(12),
   order_qty int CHECK(order_qty > 0) ENABLED,
   PRIMARY KEY(cust_id, order_date) ENABLED
);
CREATE TABLE

ALTER TABLE 可以对现有约束启用强制执行。以下语句通过对指定的约束 C_UNIQUE 启用强制执行来修改表 customer_dimension：


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

强制执行级别优先级

表和列的强制执行设置优先于强制执行参数设置。如果表或列约束省略 ENABLED 或 DISABLED，Vertica 将使用相关配置参数的当前设置。

重要

更改约束强制执行参数对省略 ENABLED 或 DISABLED 的现有表约束没有影响。这些表约束将保留它们先前获取的强制执行设置。您只能使用 ALTER TABLE...ALTER CONSTRAINT 更改这些约束的强制执行设置。

下面的 CREATE TABLE 语句创建其中包含列 order_id 和 order_qty 的表 new_sales，这两列分别定义有约束 PRIMARY KEY 和 CHECK：

=> CREATE TABLE new_sales ( order_id int PRIMARY KEY, order_qty int CHECK (order_qty > 0) );

这两个约束都没有显式启用或禁用，因此 Vertica 使用配置参数 EnableNewPrimaryKeysByDefault 和 EnableNewCheckConstraintsByDefault 在表定义中设置强制执行：

=> SHOW CURRENT EnableNewPrimaryKeysByDefault, EnableNewCheckConstraintsByDefault;
  level  |                name                | setting
---------+------------------------------------+---------
 DEFAULT | EnableNewPrimaryKeysByDefault      | 0
 DEFAULT | EnableNewCheckConstraintsByDefault | 1
(2 rows)

=> SELECT EXPORT_TABLES('','new_sales');

...

CREATE TABLE public.new_sales
(
    order_id int NOT NULL,
    order_qty int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) DISABLED,
    CONSTRAINT C_CHECK CHECK ((new_sales.order_qty > 0)) ENABLED
);

(1 row)

在这种情况下，将 EnableNewPrimaryKeysByDefault 更改为 1（启用）对表 new_sales 中的 C_PRIMARY 约束没有影响。可以使用 ALTER TABLE...ALTER CONSTRAINT 强制执行此约束：

=> ALTER TABLE public.new_sales ALTER CONSTRAINT C_PRIMARY ENABLED;
ALTER TABLE

6.2 - 验证约束强制执行

SHOW CURRENT 可以返回约束强制执行参数的设置：

=> SHOW CURRENT EnableNewCheckConstraintsByDefault, EnableNewUniqueKeysByDefault, EnableNewPrimaryKeysByDefault;
  level   |                name                | setting
----------+------------------------------------+---------
 DEFAULT  | EnableNewCheckConstraintsByDefault | 1
 DEFAULT  | EnableNewUniqueKeysByDefault       | 0
 DATABASE | EnableNewPrimaryKeysByDefault      | 1
(3 rows)

您还可以查询下面的系统表以检查表强制执行设置：

例如，以下语句查询 TABLE_CONSTRAINTS 并返回数据库表中的所有约束。对于所有可以启用或禁用的约束（PRIMARY KEY、UNIQUE 和 CHECK），is_enabled 列设置为 true 或 false：

=> SELECT constraint_name, table_name, constraint_type, is_enabled FROM table_constraints ORDER BY is_enabled, table_name;
      constraint_name      |      table_name       | constraint_type | is_enabled
---------------------------+-----------------------+-----------------+------------
 C_PRIMARY                 | call_center_dimension | p               | f
 C_PRIMARY                 | date_dimension        | p               | f
 C_PRIMARY                 | employee_dimension    | p               | f
 C_PRIMARY                 | online_page_dimension | p               | f
 C_PRIMARY                 | product_dimension     | p               | f
 C_PRIMARY                 | promotion_dimension   | p               | f
 C_PRIMARY                 | shipping_dimension    | p               | f
 C_PRIMARY                 | store_dimension       | p               | f
 C_UNIQUE_1                | tabletemp             | u               | f
 C_PRIMARY                 | vendor_dimension      | p               | f
 C_PRIMARY                 | warehouse_dimension   | p               | f
 C_PRIMARY                 | customer_dimension    | p               | t
 C_PRIMARY                 | new_sales             | p               | t
 C_CHECK                   | new_sales             | c               | t
 fk_inventory_date         | inventory_fact        | f               |
 fk_inventory_product      | inventory_fact        | f               |
 fk_inventory_warehouse    | inventory_fact        | f               |
 ...

以下查询返回具有主键、唯一性和检查约束的所有表，并显示是否启用了约束：

=> SELECT table_name, constraint_name, constraint_type, is_enabled FROM constraint_columns
   WHERE constraint_type in ('p', 'u', 'c')
   ORDER BY table_name, constraint_type;

=> SELECT table_name, constraint_name, constraint_type, is_enabled FROM constraint_columns WHERE constraint_type in ('p', 'u', 'c') ORDER BY table_name, constraint_type;
      table_name       | constraint_name | constraint_type | is_enabled
-----------------------+-----------------+-----------------+------------
 call_center_dimension | C_PRIMARY       | p               | f
 customer_dimension    | C_PRIMARY       | p               | t
 customer_dimension2   | C_PRIMARY       | p               | t
 customer_dimension2   | C_PRIMARY       | p               | t
 date_dimension        | C_PRIMARY       | p               | f
 employee_dimension    | C_PRIMARY       | p               | f
 new_sales             | C_CHECK         | c               | t
 new_sales             | C_PRIMARY       | p               | t
 ...

6.3 - 报告约束违规

Vertica 在以下两种情况下报告约束违规：

ALTER TABLE 尝试对已包含数据的表启用约束强制执行，但数据不符合约束。
DML 操作尝试在已强制执行约束的表上添加或更新数据，但新数据不符合一个或多个约束。

DDL 约束违规

当您使用 ALTER TABLE...ADD CONSTRAINT 或 ALTER TABLE...ALTER CONSTRAINT 对现有表启用约束强制执行时，Vertica 会将该约束立即应用于表的当前内容。如果 Vertica 检测到约束违规，Vertica 会返回一个报告违规的错误，然后回退 ALTER TABLE 语句。

例如：

=> ALTER TABLE public.customer_dimension ADD CONSTRAINT unique_cust_types UNIQUE (customer_type) ENABLED;
ERROR 6745:  Duplicate key values: 'customer_type=Company'
-- violates constraint 'public.customer_dimension.unique_cust_types'
DETAIL:  Additional violations:
Constraint 'public.customer_dimension.unique_cust_types':
duplicate key values: 'customer_type=Individual'

DML 约束违规

当您调用 DML 操作以在已强制执行约束的表上添加或更新数据时，Vertica 会检查新数据是否符合这些约束。如果 Vertica 检测到约束违规，此操作会返回一个报告违规的错误，然后执行回退。

例如，使用相同的主键和检查约束来定义 store_orders 和 store_orders_2015 表。这两个表都可以强制执行主键约束；只有 store_orders_2015 强制执行检查约束：

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

ALTER TABLE public.store_orders ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_no) ENABLED;
ALTER TABLE public.store_orders ADD CONSTRAINT C_CHECK CHECK (((date_part('year', store_orders.order_date))::int = 2014)) DISABLED;

CREATE TABLE public.store_orders_2015
(
    order_no int NOT NULL,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY ((date_part('year', store_orders_2015.order_date))::int);

ALTER TABLE public.store_orders_2015 ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_no) ENABLED;
ALTER TABLE public.store_orders_2015 ADD CONSTRAINT C_CHECK CHECK (((date_part('year', store_orders_2015.order_date))::int = 2015)) ENABLED;

如果您尝试将具有重复键值的数据插入 store_orders 中，则插入操作会返回错误消息。错误消息包含有关第一次违规的详细信息。它还返回有关后续违规（最多前 30 个）的简短信息。如有必要，错误消息还会包括一条说明违规次数超过 30 的注释：

=> INSERT INTO store_orders SELECT order_number, date_ordered, shipper_name, date_shipped FROM store.store_orders_fact;
ERROR 6745:  Duplicate key values: 'order_no=10' -- violates constraint 'public.store_orders.C_PRIMARY'
DETAIL:  Additional violations:
Constraint 'public.store_orders.C_PRIMARY':
duplicate key values:
'order_no=11'; 'order_no=12'; 'order_no=13'; 'order_no=14'; 'order_no=15'; 'order_no=17';
'order_no=21'; 'order_no=23'; 'order_no=26'; 'order_no=27'; 'order_no=29'; 'order_no=33';
'order_no=35'; 'order_no=38'; 'order_no=39'; 'order_no=4'; 'order_no=41'; 'order_no=46';
'order_no=49'; 'order_no=6'; 'order_no=62'; 'order_no=67'; 'order_no=68'; 'order_no=70';
'order_no=72'; 'order_no=75'; 'order_no=76'; 'order_no=77'; 'order_no=79';
Note: there were additional errors

同样，以下将数据从 store_orders 复制到 store_orders_2015 的尝试违反了表的检查约束。它返回一条类似以上消息的错误消息：


=> SELECT COPY_TABLE('store_orders', 'store_orders_2015');
NOTICE 7636:  Validating enabled constraints on table 'public.store_orders_2015'...
ERROR 7231:  Check constraint 'public.store_orders_2015.C_CHECK' ((date_part('year', store_orders_2015.order_date))::int = 2015)
violation in table 'public.store_orders_2015': 'order_no=101,order_date=2007-05-02 00:00:00'
DETAIL:  Additional violations:
Check constraint 'public.store_orders_2015.C_CHECK':violations:
'order_no=106,order_date=2016-07-01 00:00:00'; 'order_no=119,order_date=2016-01-04 00:00:00';
'order_no=14,order_date=2016-07-01 00:00:00'; 'order_no=154,order_date=2016-11-06 00:00:00';
'order_no=156,order_date=2016-04-10 00:00:00'; 'order_no=171,order_date=2016-10-08 00:00:00';
'order_no=203,order_date=2016-03-01 00:00:00'; 'order_no=204,order_date=2016-06-09 00:00:00';
'order_no=209,order_date=2016-09-07 00:00:00'; 'order_no=214,order_date=2016-11-02 00:00:00';
'order_no=223,order_date=2016-12-08 00:00:00'; 'order_no=227,order_date=2016-08-02 00:00:00';
'order_no=240,order_date=2016-03-09 00:00:00'; 'order_no=262,order_date=2016-02-09 00:00:00';
'order_no=280,order_date=2016-10-10 00:00:00';
Note: there were additional errors

用来添加或更新表内容的分区管理函数也必须遵守目标表中强制执行的约束。例如，以下 MOVE_PARTITIONS_TO_TABLE 操作尝试将分区从 store_orders 移至 store_orders_2015。但是，源分区包含违反目标表检查约束的数据。因此，该函数返回的结果表明它未能移动任何数据：

=> SELECT MOVE_PARTITIONS_TO_TABLE ('store_orders','2014','2014','store_orders_2015');
NOTICE 7636:  Validating enabled constraints on table 'public.store_orders_2015'...
             MOVE_PARTITIONS_TO_TABLE
--------------------------------------------------
 0 distinct partition values moved at epoch 204.

6.4 - 约束强制执行和锁定

对于需要验证启用的主键和唯一约束的 DML 操作，Vertica 使用插入/验证 (IV) 锁。

在强制执行主键或唯一键约束的表上运行此类操作时，Vertica 将按以下方式对表设置锁：

设置 I（插入）锁以加载数据。多个会话可以同时获取同一张表的 I 锁，并同时加载数据。
设置表的 IV 锁，可针对表的主约束和唯一约束验证加载的数据。一次只有一个会话可以获取给定表的 IV 锁。其他需要访问该表的会话将被阻止，直到 IV 锁释放为止。会话将保留其 IV 锁，直到出现以下两个事件之一：
- 验证已完成且已提交 DML 操作。
- 检测到约束违规且已回滚操作。
在上述任一情况下，Vertica 均会释放 IV 锁。

阻止锁定 IV

Vertica 验证表的主键或唯一键约束时，将暂时阻止对表执行其他 DML 操作。当多个会话同时尝试对同一个表上的数据进行大量更改时，此类延迟可能会特别明显。

例如，在三个并发会话中，每个会话均尝试将数据加载到表 t1 中，如下所示：

所有三个会话均获取 t1 的 I 锁，并开始将数据加载到表中。
会话 2 获取 t1 的排他 IV 锁，以验证其加载数据的表约束。一次只有一个会话可以获取表的 IV 锁，因此会话 1 和 3 必须等待会话 2 完成验证，然后才能开始自己的验证。
会话 2 成功验证它加载到 t1 中的所有数据。提交其加载事务之后，它将释放表的 IV 锁。
会话 1 获取 t1 的 IV 锁，并开始验证其加载的数据。在此示例中，Vertica 检测到违反约束并回滚加载事务。会话 1 释放其 t1 IV 锁。
现在，会话 3 获取 t1 的 IV 锁，并开始验证其加载的数据。完成验证后，会话 3 提交其加载事务，并释放 t1 的 IV 锁。该表现在可用于其他 DML 操作。

另请参阅

有关锁定架构和兼容性以及转换矩阵的信息，请参阅锁定模式。另请参阅 LOCKS 和 LOCK_USAGE。

6.5 - 约束强制执行和性能

在某些情况下，强制执行约束会显著影响整体系统性能。当对需要频繁进行并发批量更新的大型事实表强制执行约束时尤其如此。每个调用约束强制执行的更新操作都要求 Vertica 检查每个表行是否存在所有约束违规。因此，对具有大量数据的表强制执行多个约束可能会导致显著延迟。

为了最大限度地减少因强制执行约束而产生的开销，请忽略对经常更新的大型表强制执行约束。您可以通过在非高峰时段运行 ANALYZE_CONSTRAINTS 来评估这些表是否出现约束违规。

约束强制执行的多个方面会对系统性能产生特定的影响。这些包括：

表锁定
强制执行的约束的投影
事务内由约束触发的回退

表锁定

如果针对表强制执行约束，Vertica 在 DML 操作期间会在该表上设置插入/验证 (IV) 锁定，同时进行验证。一次只有一个会话可以获取该表上的 IV 锁定。只要会话持有这个锁定，其他会话就不能访问该表。长时间加载容易导致性能瓶颈，尤其是当多个会话尝试同时加载同一个表时。有关详细信息，请参阅约束强制执行和锁定。

强制执行的约束投影

为了强制执行主键和唯一约束，Vertica 创建了用于验证数据的特殊投影。根据锚表中的数据量，创建投影可能会产生巨大的系统开销。

事务回退

Vertica 针对每个 SQL 语句验证强制执行的约束，并回退遇到约束违规的每个语句。您无法将约束强制执行延迟到事务提交后再执行。因此，如果多个 DML 语句包含一个事务，Vertica 会分别验证每个语句以确保符合约束，并回退任何未通过验证的语句。它仅在其中的所有语句都返回后才提交事务。

例如，您可能在一个针对其中一列强制执行 UNIQUE 的表上通过单个事务发出十条 INSERT 语句。如果第六条语句尝试在该列中插入重复值，则会回退该语句。但是，可以提交其他语句。

6.6 - 强制执行的约束的投影

为了强制执行主键和唯一约束，Vertica 创建了特殊的约束强制执行投影，用于验证新数据和更新后的数据。如果您在空表上添加约束，Vertica 仅在向该表添加数据时才会为该表创建约束强制执行投影。如果您向填充有内容的表添加主键或唯一约束并启用强制执行，Vertica 会选择现有投影来强制执行约束（如果存在）。否则，Vertica 会为该约束创建投影。如果出现约束违规，Vertica 会回退语句以及它为约束创建的任何投影。

如果您删除已强制执行的主键或唯一约束，Vertica 会自动删除与该约束关联的投影。您还可以使用 DROP PROJECTION 显式删除约束投影。如果该语句省略了 CASCADE，Vertica 会发出一条警告，提示为已启用的约束删除此投影；否则，它会静默删除此投影。无论哪种情况，下次 Vertica 需要强制执行此约束时，它都会重新创建投影。根据锚表中的数据量，创建投影可能会产生巨大的开销。

您可以查询系统表 PROJECTIONS 的布尔列 IS_KEY_CONSTRAINT_PROJECTION 以获得特定于约束的投影。

注意

表中的约束强制执行投影可以通过 ANALYZE_CONSTRAINTS 显著促进约束分析。

6.7 - 约束强制执行限制

Vertica 不支持对外键或外部表强制执行约束。限制也适用于临时表。

外键

Vertica 不支持强制执行外键和引用完整性。因此，在以下情况下，在加载数据时可能会返回错误：

处理内部联接查询时。
由于存在外键而将外部联接视为内部联接时。

要验证外键约束，请使用 ANALYZE_CONSTRAINTS。

外部表

Vertica 不支持对外部表自动强制执行约束。

本地临时表

ALTER TABLE 仅当表中不包含数据时，才能对本地临时表中的主键或唯一约束设置强制执行。如果您尝试在包含数据的表中强制执行约束，ALTER TABLE 会返回错误。

全局临时表

在全局临时表中，只能使用 CREATE TEMPORARY TABLE 对主键或唯一约束设置强制执行。 ALTER TABLE 在您尝试对现有表（无论已填充还是为空）中的主键或唯一约束设置强制执行时返回错误。

注意

您始终可以使用 ALTER TABLE...DROP CONSTRAINT 来禁用本地和全局临时表中的主键和唯一键约束。