Projections for Enabled Primary and Unique Key Constraints

To enforce primary and unique key constraints, Vertica creates special key projections as needed in response to DML or DDL, which are checked for constraint violations. If a constraint violation occurs, Vertica rolls back the statement and any special key projection it created. The system returns an error specifying the unique or primary key constraint that was violated.

If you have added a constraint on a table that is empty, Vertica does not immediately create a special key projection for that constraint. Vertica defers creation of a special key projection until the first row of data is added to the table using a DML or COPY statement. If you add a constraint to a populated table, Vertica chooses an existing projection for enforcement of the constraint, if possible. If none of the existing projections are sufficient to validate the constraint, Vertica creates a new projection for the enabled constraint.

You can check primary and unique key constraint projections by querying the PROJECTIONS systems table under the V_CATALOG Schema. Each entry applying to a key constraint projection include the column name IS_KEY_CONSTRAINT_PROJECTION.

If you drop an enabled primary or unique key constraint, the system may drop an associated projection if one was automatically created. You can drop a specific projection even if a key constraint is enabled:

In either case, the next time Vertica needs to enforce the constraint for DML, the system creates a new special key projection, unless an existing projection can enforce the same enabled constraint. The time it takes to regenerate a key projection depends upon the volume of the table.

Note: If you subsequently use ANALYZE_CONSTRAINTS on a table that has enabled primary or unique key constraints (and thus their associated projections), ANALYZE_CONSTRAINTS can leverage the projections previously created for enforcement, resulting in a performance improvement for ANALYZE_CONSTRAINTS.