Levels of Constraint Enforcement
Constraints can be enforced at two levels:
Constraint Enforcement Parameters
Vertica supports three Boolean parameters to enforce constraints:
Enforcement parameter | Default setting |
---|---|
EnableNewPrimaryKeysByDefault
|
0 (false/disabled) |
EnableNewUniqueKeysByDefault
|
0 (false/disabled) |
EnableNewCheckConstraintsByDefault
|
1 (true/enabled) |
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 );
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.
Changing constraint enforcement parameters has no effect on existing table constraints that omit ENABLED
or DISABLED
. These table constraints retain the enforcement settings that they previously acquired. You can change the enforcement settings on these constraints only with ALTER TABLE…ALTER CONSTRAINT
.
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 ); ALTER TABLE public.new_sales ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) DISABLED; ALTER TABLE public.new_sales ADD CONSTRAINT C_CHECK CHECK ((new_sales.order_qty > 0)) ENABLED;
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;