Constraint Enforcement
You can enforce the following constraints:
PRIMARY KEY
UNIQUE
CHECK
When you enable constraint enforcement on a table, Vertica applies that constraint immediately to the table's current content, and to all content that is added or updated later.
Operations that Invoke Constraint Enforcement
The following DDL and DML operations invoke constraint enforcement:
ALTER TABLE…ADD CONSTRAINT
andALTER TABLE…ALTER CONSTRAINT
INSERT
andINSERT…SELECT
COPY
UPDATE
MERGE
- Partitioning functions:
-COPY_PARTITIONS_TO_TABLE
-MOVE_PARTITIONS_TO_TABLE
-SWAP_PARTITIONS_BETWEEN_TABLES
Benefits and Costs
Enabling constraint enforcement can help minimize post-load maintenance tasks, such as validating data separately with ANALYZE_CONSTRAINTS
, and then dealing with the constraint violations that it returns.
Enforcing key constraints, particularly on primary keys, can help the optimizer produce faster query plans, particularly for joins. When a primary key constraint is enforced on a table, the optimizer assumes that no rows in that table contain duplicate key values.
Under certain circumstances, widespread constraint enforcement, especially in large fact tables, can incur significant system overhead. For details, see Constraint Enforcement and Performance.
In This Section