Unique Constraints

Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all rows in the table.

How to Verify Unique Constraints

Vertica allows you to add a (non-enabled) unique constraint to a column. You can then insert data into that column, regardless of whether that constraint is not unique with respect to other values in that column. If your data does not conform to the declared non-enabled constraints, your queries could yield unexpected results.

You can use ANALYZE_CONSTRAINTS to check for constraint violations, or you can enable automatic enforcement of unique key constraints. For more information on enabling and disabling unique key constraints, refer to Enforcing Primary Key, Unique Key, and Check Constraints Automatically.

Add Unique Column Constraints

There are several ways to add a unique constraint on a column. If you use the CONSTRAINT keyword, you must specify a constraint name. The following example adds a UNIQUE constraint on the product_key column and names it product_key_UK:

CREATE TABLE product (product_key INTEGER NOT NULL CONSTRAINT product_key_UK UNIQUE,
   ...
);

Vertica recommends naming constraints, but it is optional:

CREATE TABLE product (product_key INTEGER NOT NULL UNIQUE,
   ...
);

You can specify the constraint after the column definition, with and without naming it:

CREATE TABLE product (product_key INTEGER NOT NULL,
   ...,
   CONSTRAINT product_key_uk UNIQUE (product_key)
);
CREATE TABLE product (
   product_key INTEGER NOT NULL,
   ...,
   UNIQUE (product_key)
);

You can also use ALTER TABLE to specify a unique constraint. This example names the constraint product_key_UK:

ALTER TABLE product ADD CONSTRAINT product_key_UK UNIQUE (product_key);

You can use CREATE TABLE and ALTER TABLE to specify unique constraints on multiple columns. If a unique constraint refers to a group of columns, separate the column names using commas. The column listing specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique:

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