Levels of constraint enforcement

Constraints can be enforced at two levels:

• Database configuration parameters

• Table constraints

Constraint enforcement parameters

Vertica supports three Boolean parameters to enforce constraints:

Table constraint enforcement

You set constraint enforcement on tables through CREATE TABLE and ALTER TABLE, by qualifying the constraints with the keywords ENABLED or DISABLED. The following CREATE TABLE statement enables enforcement of a check constraint in its definition of column order_qty:

=> CREATE TABLE new_orders (
   cust_id int,
   order_date timestamp DEFAULT CURRENT_TIMESTAMP,
   product_id varchar(12),
   order_qty int CHECK(order_qty > 0) ENABLED,
   PRIMARY KEY(cust_id, order_date) ENABLED
);
CREATE TABLE

ALTER TABLE can enable enforcement on existing constraints. The following statement modifies table customer_dimension by enabling enforcement on named constraint C_UNIQUE:

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

Enforcement level precedence

Table and column enforcement settings have precedence over enforcement parameter settings. If a table or column constraint omits ENABLED or DISABLED, Vertica uses the current settings of the pertinent configuration parameters.

The following CREATE TABLE statement creates table new_sales with columns order_id and order_qty, which are defined with constraints PRIMARY KEY and CHECK, respectively:

=> CREATE TABLE new_sales ( order_id int PRIMARY KEY, order_qty int CHECK (order_qty > 0) );

Neither constraint is explicitly enabled or disabled, so Vertica uses configuration parameters EnableNewPrimaryKeysByDefault and EnableNewCheckConstraintsByDefault to set enforcement in the table definition:

=> SHOW CURRENT EnableNewPrimaryKeysByDefault, EnableNewCheckConstraintsByDefault;
  level  |                name                | setting
---------+------------------------------------+---------
 DEFAULT | EnableNewPrimaryKeysByDefault      | 0
 DEFAULT | EnableNewCheckConstraintsByDefault | 1
(2 rows)

=> SELECT EXPORT_TABLES('','new_sales');
...

CREATE TABLE public.new_sales
(
    order_id int NOT NULL,
    order_qty int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) DISABLED,
    CONSTRAINT C_CHECK CHECK ((new_sales.order_qty > 0)) ENABLED
);

(1 row)

In this case, changing EnableNewPrimaryKeysByDefault to 1 (enabled) has no effect on the C_PRIMARY constraint in table new_sales. You can enforce this constraint with ALTER TABLE...ALTER CONSTRAINT:

=> ALTER TABLE public.new_sales ALTER CONSTRAINT C_PRIMARY ENABLED;
ALTER TABLE