报告约束违规

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.