Constraint Enforcement Parameters

The following configuration parameters enforce constraints.

Use the ALTER DATABASE statement to set these parameters. You do not need to restart your database after setting them.

The parameter settings apply for any check constraint, and any primary or unique key constraint that you have not explicitly enabled or disabled within a CREATE TABLE or ALTER TABLE statement.
Any new check constraint, and any primary or unique key constraint that you create or alter is set according to the value of the corresponding parameter unless you specifically enabled or disabled the constraint.

Important: Setting a constraint as enabled or disabled when you create or alter it using CREATE TABLE or ALTER TABLE overrides the parameter setting.

Parameters	Description
EnableNewCheckConstraintsByDefault	Set to 1 (the default) to automatically enable newly created check constraints that you specified through CREATE TABLE or ALTER TABLE statements. However, if you have explicitly disabled a constraint when you created or altered it, it is not enforced. Default Value: 1 (Enabled) Example: ALTER DATABASE mydb SET EnableNewCheckConstraintsByDefault = 0;
EnableNewPrimaryKeysByDefault	Set to 1 to automatically enable newly created primary key constraints that you specified through CREATE TABLE or ALTER TABLE statements. However, if you have explicitly disabled a constraint when you created or altered it, it is not enforced. Default Value: 0 (Disabled) Example: ALTER DATABASE mydb SET EnableNewPrimaryKeysByDefault = 1;
EnableNewUniqueKeysByDefault	Set to 1 to automatically enable newly created unique constraints that you specified through CREATE TABLE or ALTER TABLE statements. However, if you have explicitly disabled a constraint when you created or altered it, it is not enforced. Default Value: 0 (Disabled) Example: ALTER DATABASE mydb SET EnableNewUniqueKeysByDefault = 1;

Parameters

Description

EnableNewCheckConstraintsByDefault

Set to 1 (the default) to automatically enable newly created check constraints that you specified through CREATE TABLE or ALTER TABLE statements. However, if you have explicitly disabled a constraint when you created or altered it, it is not enforced.

Default Value: 1 (Enabled)

Example:

ALTER DATABASE mydb SET EnableNewCheckConstraintsByDefault = 0;

EnableNewPrimaryKeysByDefault

Set to 1 to automatically enable newly created primary key constraints that you specified through CREATE TABLE or ALTER TABLE statements. However, if you have explicitly disabled a constraint when you created or altered it, it is not enforced.

Default Value: 0 (Disabled)

Example:

ALTER DATABASE mydb SET EnableNewPrimaryKeysByDefault = 1;

EnableNewUniqueKeysByDefault

Set to 1 to automatically enable newly created unique constraints that you specified through CREATE TABLE or ALTER TABLE statements. However, if you have explicitly disabled a constraint when you created or altered it, it is not enforced.

Default Value: 0 (Disabled)

Example:

ALTER DATABASE mydb SET EnableNewUniqueKeysByDefault = 1;

Note: Vertica recommends enabling primary key enforcement if you have enabled unique key enforcement.