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
);