Unique constraints

You can specify a unique constraint on a column so each value in that column is unique among all other values. You can define a unique constraint when you create a table, or you can add a unique constraint to an existing table with ALTER TABLE. You cannot use a uniqueness constraint on a column with a collection type.

For example, the following ALTER TABLE statement defines the sku_number column in the product_dimensions table as unique:

=> ALTER TABLE public.product_dimension ADD UNIQUE(sku_number);
WARNING 4887:  Table product_dimension has data. Queries using this table may give wrong results
if the data does not satisfy this constraint
HINT:  Use analyze_constraints() to check constraint violation on data

Enforcing unique constraints

You enforce unique constraints globally with configuration parameter EnableNewUniqueKeysByDefault. You can also enforce unique constraints for specific tables by qualifying their unique constraints with the keyword ENABLED. In both cases, Vertica checks values as they are loaded into unique columns, and returns with errors on any constraint violations. Alternatively, you can use ANALYZE_CONSTRAINTS to validate unique constraints after updating table contents. For details, see Constraint enforcement.

For example, the previous example does not enforce the unique constraint in column sku_number. The following statement enables this constraint:

=> ALTER TABLE public.product_dimension ALTER CONSTRAINT C_UNIQUE ENABLED;
ALTER TABLE

Multi-column unique constraints

You can define a unique constraint that is comprised of multiple columns. The following CREATE TABLE statement specifies that the combined values of columns c1 and c2 in each row must be unique among all other rows:

CREATE TABLE dim1 (c1 INTEGER,
    c2 INTEGER,
    c3 INTEGER,
  UNIQUE (c1, c2) ENABLED
);