Enforcing Primary Key and Foreign Key Constraints

Enforcing (Non-Enabled) Primary Key Constraints

Unless you enable enforcement of primary key constraints, Vertica does not enforce the uniqueness of primary key values when they are loaded into a table. Thus, a key enforcement error can occur unless one dimension row uniquely matches each foreign key value when the table is joined to a dimension table during a query:

Note: Consider using sequences or auto-incrementing columns for primary key columns, which guarantees uniqueness and avoids the constraint enforcement problem and associated overhead. For more information, see Using Sequences.

For information on automatic enforcement of primary key constraints during DML, see Enforcing Primary Key, Unique Key, and Check Constraints Automatically.

Foreign Key Constraint Violations

A table's foreign key constraints are not enforced during data load. Thus, it is possible to load data that causes a constraint violation. Subsequently, a constraint violation error can occur when:

An inner join query is processed.
An outer join is treated as an inner join due to the presence of foreign key.

Detecting Constraint Violations Before You Commit Data

To detect constraint violations, you can load data without committing it using the COPY statement with the NO COMMIT option, and then perform a post-load check using the ANALYZE_CONSTRAINTS function. If constraint violations exist, you can roll back the load because you have not committed it. For more details, see Detecting Constraint Violations with ANALYZE_CONSTRAINTS.

You can also configure your system to automatically enforce primary key, unique key, and check constraints during DML. For information on automatic enforcement, see Enforcing Primary Key, Unique Key, and Check Constraints Automatically.