Enabled Mode

Database objects in the enabled mode behave as constraints, indexes, or triggers during DML operations on the table.

If you specify no database object mode when constraints, indexes, or triggers are created, they are enabled by default. The data definition statements CREATE TABLE, ALTER TABLE, CREATE INDEX, and CREATE TRIGGER all create database objects in enabled mode, unless you explicitly specify a different mode.

Which nondefault object modes are available at creation-time depends on the type of object:
  • When a trigger or a non-unique index is created, the only keyword alternative to the enabled mode is DISABLED.
  • When a constraint or a unique index is created, alternatives to the default or explicit ENABLED keyword include DISABLED, FILTERING WITH ERROR, and FILTERING WITHOUT ERROR. (But if you only specify FILTERING, then FILTERING WITHOUT ERROR is the default error mode for FILTERING objects.)
  • While the ALTER TABLE ADD CONSTRAINT statement is creating a foreign-key or check constraint, however, any of these three modes can instead be specified as additional alternatives to the enabled mode:
    • ENABLED NOVALIDATE
    • FILTERING WITH ERROR NOVALIDATE
    • FILTERING WITHOUT ERROR NOVALIDATE.

When the SET Database Object Mode statement changes the mode of an existing constraint, index, or trigger, however, there no default mode. If you specify no object mode, the SET Database Object Mode statement fails with error -201. If you want to reset the mode of a constraint, index, or trigger to enabled from some other mode, you must explicitly specify the ENABLED keyword.

When a database object is successfully enabled, the database server registers that object state in the sysobjstate table of the system catalog, and takes that database object into consideration when its table is the target of a subsequent INSERT, DELETE, MERGE, or UPDATE statement (or for Select triggers, a SELECT statement). Thus, an enabled constraint is enforced, an enabled index is updated, and an enabled trigger on a table is executed when the trigger event takes place.

For example, after you set foreign-key constraints and unique indexes to enabled mode, when an INSERT, DELETE, MERGE, or UPDATE operation attempts to violate the referential integrity of the table, the data manipulation operation fails, no rows in the table are changed, and the database server returns an error message.

ENABLED NOVALIDATE mode for foreign-key or check constraints

While the SET Database Object Mode statement is changing the mode of a foreign-key or check constraint to ENABLED, the database server validates the constraint by examining every row in the constrained table to verify the constraint conditions. This validation can require significant time and resources. You can instead bypass the search for violating rows during the SET Database Object mode operation by including the NOVALIDATE keyword to change the constraint mode to ENABLED NOVALIDATE. For large tables, specifying ENABLED NOVALIDATE can substantially reduce the time required to enable the constraint.

After the SET CONSTRAINTS option to the SET Database Object Mode statement successfully enables a foreign-key or check constraint, the constraint mode is registered as enabled (E) in the sysobjstate system catalog table. The NOVALIDATE keyword, that had prevented checking for referential-integrity or check condition violations while the SET CONSTRAINTS statement was running, is not encoded anywhere in the system catalog, and has no further effect on the object mode or the behavior of the constraint.

Until it is dropped or disabled, that constraint is enforced during subsequent DML operations on its table, in order to maintain the referential integrity of the database.