Altering or Dropping an Existing Validation Directive

Validation directives can be enabled or disabled and dropped when no longer needed.

Use ALTER DIRECTIVE to enable or disable an existing validation directive without dropping and recreating it.

Syntax:

ALTER DIRECTIVE directive_name ENABLE;

or

ALTER DIRECTIVE directive_name DISABLE;

Example 9-2 Altering an Existing Validation Directive

Consider one of the create directive examples listed in the previous section:

CREATE OR REPLACE DIRECTIVE name_only_king FOR employee_dv
  VALIDATE
  ON SELECT
  AFTER OBJECT
  NOVALIDATE
  USING json_value(new.data, '$.employeeName') = 'KING';

You might disable it to troubleshoot application behavior or to avoid validation overhead temporarily using:

ALTER DIRECTIVE name_only_king DISABLE;

The directive definition remains in the database, but it is not evaluated for subsequent SELECT operations on employee_dv while disabled.

You can re-enable the validation using:

ALTER DIRECTIVE name_only_king ENABLE;

You cannot change the directive’s processing stage (BEFORE OBJECT, AFTER OBJECT, ON COMMIT), ON action list, or the USING validation logic. To make those changes, you must DROP DIRECTIVE and create a new one with the desired definition.

Example 9-3 Dropping an Existing Validation Directive

This example drops the name_only_king directive created in the previous example:

DROP DIRECTIVE name_only_king;