I will gather in this article several queries, code blocks that I use as a practician when managing constraints.

This article will be updated periodically with new hints.

Identify the disabled constraints for a schema

SELECT constraint_name
       , constraint_type
       , table_name
       , status
    FROM all_constraints
    WHERE owner = '&SCHEMA_NAME'
    AND status = 'DISABLED';

Enable disabled constraints per schema

BEGIN
   FOR rec IN (SELECT owner, table_name, constraint_name 
               FROM all_constraints 
               WHERE owner = '&SCHEMA_NAME' 
               AND CONSTRAINT_TYPE = 'P'
               AND status = 'DISABLED') 
   LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' ENABLE CONSTRAINT ' || rec.constraint_name;
   END LOOP;
END;
/
BEGIN
   FOR rec IN (SELECT owner, table_name, constraint_name 
               FROM all_constraints 
               WHERE owner = '&SCHEMA_NAME' 
               AND CONSTRAINT_TYPE = 'R'
               AND status = 'DISABLED') 
   LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' ENABLE CONSTRAINT ' || rec.constraint_name;
   END LOOP;
END;
/

Non-Validated Constraints

In Oracle, non-validated constraints are constraints that are added to a table without immediately checking if all existing rows conform to the constraint. This can be useful when you need to add a constraint to a large table without locking or validating all rows at once, potentially reducing the performance impact. However, future data inserts or updates must adhere to the constraint.

Handling Non-Validated Constraints in Oracle

Creating a Non-Validated Constraint

You can create a constraint with the NOVALIDATE option, which tells Oracle not to check existing data in the table, but enforce the constraint for new data going forward.

Example:

ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
NOVALIDATE;

In this example, the foreign key emp_dept_fk is added to the employees table, but existing rows are not validated. Future inserts and updates will, however, be checked against the constraint.

• The NOVALIDATE clause is supported for:
  • Primary Key (PRIMARY KEY)
  • Foreign Key (FOREIGN KEY)
  • Check Constraints (CHECK)
  • Unique Constraints (UNIQUE)

Viewing Non-Validated Constraints

To check if a constraint is non-validated, you can query the USER_CONSTRAINTS or DBA_CONSTRAINTS views.

• Query Example:

SELECT constraint_name, constraint_type, status, validated
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

The VALIDATED column will show NOT VALIDATED for constraints that were created with NOVALIDATE.

Validating an Existing Non-Validated Constraint

You can validate a non-validated constraint later when it’s more convenient for the system, such as during off-peak hours.

• Example:

ALTER TABLE employees
MODIFY CONSTRAINT emp_dept_fk VALIDATE;

This statement will validate the emp_dept_fk foreign key constraint on the employees table. If any existing rows violate the constraint, the statement will fail, and the validation will not complete.

Adding a Constraint with Deferred Validation

Another approach is to defer the validation of constraints using the DEFERRABLE option. With this, Oracle allows you to decide when to enforce the constraint (e.g., at the end of the transaction).

• Example:

ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
DEFERRABLE INITIALLY DEFERRED;

In this case, the constraint is checked only at the end of a transaction, not immediately after each DML operation (e.g., insert or update).

• Options:
  • INITIALLY DEFERRED: The constraint is deferred by default, checked at the end of the transaction.
  • INITIALLY IMMEDIATE: The constraint is checked immediately after each operation but can be deferred later.

Disabling and Re-enabling Constraints

You can temporarily disable a constraint, load or modify data, and then enable the constraint with or without validation.

Disable Constraint:

ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;

Enable Without Validation:

ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT emp_dept_fk;

Enable with Validation:

ALTER TABLE employees ENABLE VALIDATE CONSTRAINT emp_dept_fk;

Enabling with validation ensures that the constraint is checked for both existing and new data.

Common Use Cases for Non-Validated Constraints

• Large datasets: Adding constraints without validating existing data to avoid long lock times or high resource consumption.
• Phased constraint enforcement: First, add the constraint with NOVALIDATE to ensure future operations comply, and later validate the existing data during a maintenance window.
• Data cleanup: Add constraints to enforce future data integrity, and separately work on cleaning up legacy data that might violate the new constraints.

Important Considerations

• Performance: Non-validated constraints are faster to apply because Oracle skips checking all existing rows. However, this can leave existing data in an inconsistent state.
• Data Integrity: Use non-validated constraints carefully, especially in critical systems where consistent data is required.
• Enforcement on New Data: Even if a constraint is not validated, Oracle will still enforce the constraint on any new or updated rows after the constraint is added.

Summary of Key Actions

Add a non-validated constraint:

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES ref_table (ref_column) NOVALIDATE;

Check the status of non-validated constraints:

SELECT constraint_name, validated FROM user_constraints WHERE table_name = 'TABLE_NAME';

Validate a non-validated constraint:

ALTER TABLE table_name MODIFY CONSTRAINT constraint_name VALIDATE;

Deferred validation (check constraint at the end of the transaction):

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES ref_table (ref_column) DEFERRABLE INITIALLY DEFERRED;

---

Tested on

I list below the database versions I tested the statements on started with the most recent one:

• Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 – Production Version 23.6.0.24.07