Enforcing Primary Key, Unique Key, and Check Constraints Automatically

When you create a new constraint with CREATE TABLE or ALTER TABLE, you can specify whether the constraint will be automatically enforced. You can also alter a constraint with ALTER TABLE (using the ALTER CONSTRAINT parameter) and specify whether it will be automatically enforced. You enable or disable individual constraints specifically using the ENABLED or DISABLED options.

In addition, you can create multi-column constraints with CREATE TABLE or ALTER TABLE. All primary key and unique key constraints are defined at the table level. Check constraints are defined at the column or table level.

By checking any system table with an is_enabled column, you can confirm whether a primary key, unique key, or check constraint is currently enabled. The system tables that include an is_enabled column are CONSTRAINT_COLUMNS, TABLE_CONSTRAINTS, and PRIMARY_KEYS.

Automatic enforcement applies to current table content and content you later add to the table.

Enabling a Constraint on an Empty Table — If you create an enabled constraint on an empty table, the constraint is enforced on any content you later add to that table.
Enabling a Constraint on a Populated Table — If you use ALTER TABLE to either enable an existing constraint or add a new constraint that is enabled, the constraint is immediately enforced for the current content, and is enforced for content you subsequently add to the table.

Important: If validation of the current content fails, Vertica completely rolls back the ALTER TABLE DDL statement that caused the failure.

If you do not specify the ENABLED or DISABLED option when you create a constraint, the system relies on the setting of the configuration parameter for the respective constraint:

EnableNewPrimaryKeysByDefault — If you specifically create a new primary key constraint but do not enable or disable it, the system relies on the value of the parameter EnableNewPrimaryKeysByDefault. If the parameter is set to 1 (enabled), the constraint you created is automatically enforced even though you did not specifically enable it when you created it.
EnableNewUniqueKeysByDefault — If you specifically create a new unique key constraint but do not enable or disable it, the system relies on the value of the parameter EnableNewUniqueKeysByDefault.
EnableNewCheckConstraintsByDefault — If you specifically create a new check constraint but do not enable or disable it, the system relies on the value of the parameter EnableNewCheckConstraintsByDefault. This parameter is the only one of the three that is set to 1 (enabled) by default.

In regards to constraint enablement, you can:

Enable or Disable a Constraint When Creating — You can specifically enable or disable when you create the constraint using CREATE TABLE or ALTER TABLE. If you do so, the constraint remains enabled or disabled regardless of the setting of the parameters EnableNewPrimaryKeysByDefault, EnableNewUniqueKeysByDefault, or EnableNewCheckConstraintsByDefault.
Create a Constraint Without Enabling or Disabling — You can also create a constraint using CREATE TABLE or add a constraint using ALTER TABLE without specifically enabling or disabling it using the ENABLED or DISABLED keyword. If you do so, Vertica looks at the setting of the parameters at the moment you create or alter the constraint to determine whether that constraint is enabled or disabled. Note that, if you alter an existing constraint using ALTER TABLE ALTER CONSTRAINT, you must specifically enable or disable it using either the ENABLED or DISABLED keyword.

Important: When creating a constraint without enabling it, Vertica uses the settings of EnableNewPrimaryKeysByDefault, EnableNewUniqueKeysByDefault, and EnableNewCheckConstraintsByDefault that are in effect at the time of creation.

The following figure summarizes constraint enablement.