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 and ALTER TABLE...ALTER CONSTRAINT

• INSERT and INSERT...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.