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

返回本页常规视图.

约束强制执行

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

  • PRIMARY KEY

  • UNIQUE

  • CHECK

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

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

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

益处和代价

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

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

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

1 - 约束强制执行级别

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

约束强制执行参数

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 将使用相关配置参数的当前设置。

下面的 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

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

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.

4 - 约束强制执行和锁定

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

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

  1. 设置 I（插入）锁以加载数据。多个会话可以同时获取同一张表的 I 锁，并同时加载数据。

  2. 设置表的 IV 锁，可针对表的主约束和唯一约束验证加载的数据。一次只有一个会话可以获取给定表的 IV 锁。其他需要访问该表的会话将被阻止，直到 IV 锁释放为止。 会话将保留其 IV 锁，直到出现以下两个事件之一：

    • 验证已完成且已提交 DML 操作。

    • 检测到约束违规且已回滚操作。

    在上述任一情况下，Vertica 均会释放 IV 锁。

阻止锁定 IV

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

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

  1. 所有三个会话均获取 t1 的 I 锁，并开始将数据加载到表中。

  2. 会话 2 获取 t1 的排他 IV 锁，以验证其加载数据的表约束。一次只有一个会话可以获取表的 IV 锁，因此会话 1 和 3 必须等待会话 2 完成验证，然后才能开始自己的验证。

  3. 会话 2 成功验证它加载到 t1 中的所有数据。提交其加载事务之后，它将释放表的 IV 锁。

  4. 会话 1 获取 t1 的 IV 锁，并开始验证其加载的数据。在此示例中，Vertica 检测到违反约束并回滚加载事务。会话 1 释放其 t1 IV 锁。

  5. 现在，会话 3 获取 t1 的 IV 锁，并开始验证其加载的数据。完成验证后，会话 3 提交其加载事务，并释放 t1 的 IV 锁。该表现在可用于其他 DML 操作。

另请参阅

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

5 - 约束强制执行和性能

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

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

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

表锁定

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

强制执行的约束投影

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

事务回退

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

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

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

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

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

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

7 - 约束强制执行限制

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

外键

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

  • 处理内部联接查询时。

  • 由于存在外键而将外部联接视为内部联接时。

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

外部表

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

本地临时表

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

全局临时表

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