Enabling or Disabling Automatic Enforcement of Individual Constraints

To enable or disable individual constraints, use the CREATE TABLE or ALTER TABLE statement with the ENABLED or DISABLED options, as shown in the following examples.

The following sample uses ALTER TABLE to create and enable a primary key constraint on a sample table called mytable.

ALTER TABLE mytable ADD CONSTRAINT primarysample PRIMARY KEY(id) ENABLED;

The following sample specifically disables the constraint.

ALTER TABLE mytable ALTER CONSTRAINT primarysample DISABLED;

The following sample uses CREATE TABLE to create a primary key constraint without explicitly enabling it. In this case, the constraint is enabled only if EnableNewPrimaryKeysByDefault is also enabled. If EnableNewPrimaryKeysByDefault is set to 1 (enabled), then this constraint is enforced. If EnableNewPrimaryKeysByDefault is at its default setting (disabled), then this constraint is not enforced.

CREATE TABLE mytable (id INT PRIMARY KEY);

The following sample uses CREATE TABLE to create a primary key constraint and enable it. This statement enables the constraint regardless of how you set the parameter EnableNewPrimaryKeysByDefault.

CREATE TABLE mytable (id INT PRIMARY KEY ENABLED);

The following example uses CREATE TABLE to create and specifically disable the check constraint, chyear. This statement disables the constraint regardless of the setting of how you set the parameter EnableNewCheckConstraintsByDefault.

CREATE TABLE emphire (hire_date INT, termination_date INT CONSTRAINT chyear 
CHECK (termination_date >= hire_date) DISABLED, firstnameof VARCHAR, lastnameof VARCHAR);