Setting Constraints

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.product_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, instead of specifying the primary key for table product_dimension when it is created, you can set this constraint with a separate ALTER TABLE…ADD CONSTRAINT statement, as follows:

                
                
                => ALTER TABLE product_dimension
                 ADD CONSTRAINT pk PRIMARY KEY (product_key, 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 in the same way. For example, when you export from the catalog the DDL for product_dimension, Vertica renders all constraints (except NOT NULL) as ALTER TABLE…ADD CONSTRAINT statements:

=> SELECT EXPORT_TABLES('','customer_dimension');
...
CREATE TABLE public.product_dimension
(
    product_key int NOT NULL,
    product_version int NOT NULL,
    product_description varchar(128),
    sku_number NOT NULL char(32),
    category_description char(32)
);
ALTER TABLE public.product_dimension2 ADD CONSTRAINT C_UNIQUE UNIQUE (sku_number) DISABLED;
ALTER TABLE public.product_dimension2 ADD CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED;