Setting constraints

You can set constraints on a new table and an existing one with CREATE TABLE and ALTER TABLE...ADD CONSTRAINT, respectively.

You can set constraints on a new table and an existing one with CREATE TABLE and ALTER TABLE...ADD CONSTRAINT, respectively.

Setting constraints on a new table

CREATE TABLE can specify a constraint in two ways: as part of the column definition, or following all column definitions.

For example, the following CREATE TABLE statement sets two constraints on column sku_number, NOT NULL and UNIQUE. After all columns are defined, the statement also sets a primary key that is composed of two columns, product_key and product_version:

=> CREATE TABLE public.prod_dimension(
    product_key int,
    product_version int,
    product_description varchar(128),
    sku_number char(32) NOT NULL UNIQUE,
    category_description char(32),
    CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED
);
CREATE TABLE

Setting constraints on an existing table

ALTER TABLE...ADD CONSTRAINT adds a constraint to an existing table. For example, the following statement specifies unique values for column product_version:

=> ALTER TABLE prod_dimension ADD CONSTRAINT u_product_versions UNIQUE (product_version) ENABLED;
ALTER TABLE

Validating existing data

When you add a constraint on a column that already contains data, Vertica immediately validates column values if the following conditions are both true:

The constraint is a primary key, unique, or check constraint.
The constraint is enforced.

If either of these conditions is not true, Vertica does not validate the column values. In this case, you must call ANALYZE_CONSTRAINTS to find constraint violations. Otherwise, queries are liable to return unexpected results. For details, see Detecting constraint violations.

Exporting table constraints

Whether you specify constraints in the column definition or on the table, Vertica stores the table DDL as part of the CREATE statement and exports them as such. One exception applies: foreign keys are stored and exported as ALTER TABLE statements.

For example:

=> SELECT EXPORT_TABLES('','prod_dimension');
...
CREATE TABLE public.prod_dimension
(
    product_key int NOT NULL,
    product_version int NOT NULL,
    product_description varchar(128),
    sku_number char(32) NOT NULL,
    category_description char(32),
    CONSTRAINT C_UNIQUE UNIQUE (sku_number) DISABLED,
    CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED,
    CONSTRAINT u_product_versions UNIQUE (product_version) ENABLED
);
(1 row)