Choosing Default Enforcement for Newly Declared or Modified Constraints
The EnableNewPrimaryKeysByDefault and EnableNewUniqueKeysByDefault parameter settings govern automatic enforcement of primary key and unique key constraints. The parameter EnableNewCheckConstraintsByDefault governs automatic enforcement of check constraints, and is the only one of the three parameters that is enabled by default.
You do not need to restart your database once you have set these parameters.
-
To enable or disable enforcement of newly created primary keys, set the parameter EnableNewPrimaryKeysByDefault. To disable, keep the default setting of 0. To enforce the constraints, set EnableNewPrimaryKeysByDefault to 1 to enable.
ALTER DATABASE VMart SET EnableNewPrimaryKeysByDefault = 1;
-
To enable or disable enforcement of newly created constraints for unique keys, set the parameter EnableNewUniqueKeysByDefault. To disable, keep the default setting of 0. Set EnableNewUniqueKeysByDefault to 1 to enable.
ALTER DATABASE VMart SET EnableNewUniqueKeysByDefault = 1;
-
To enable or disable enforcement of newly created check constraints, set the parameter EnableNewCheckConstraintsByDefault. To enable, keep the default setting of 1. Set EnableNewCheckConstraintsByDefault to 0 to disable.
ALTER DATABASE VMart SET EnableNewCheckConstraintsByDefault = 0;
You can check individual parameter values using SHOW CURRENT:
=> show current Enablenewcheckconstraintsbydefault; level | name | setting ----------+------------------------------------+--------- DATABASE | EnableNewCheckConstraintsByDefault | 0
When you upgrade to Vertica 8.1.x, the primary and unique key constraints in any tables you carry over are disabled. Existing constraints are not automatically enforced. To enable existing constraints and make them automatically enforceable, manually enable each constraint using the ALTER TABLE ALTER CONSTRAINT statement. This statement triggers constraint enforcement for the existing table contents. Statements roll back if one or more violations occur.