Tag Archives: CONSTRAINT

Foreign Key Constraints in a ORACLE DWH

There are several possibilities to have Foreign Key Constraints in a ORACLE-DWH-Environment.

ENABLE VALIDATE
ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE VALIDATE;

Enabled and validated means on every commit the constraint is validated to be correct.

ENABLE NOVALIDATE
ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
ENABLE NOVALIDATE;

You can use this option if the tables are not correct corresponding to the constraint, but you need to have a enforced constraint for future inserts.
That means the current state of the existing data is not validated but you can’t insert data in the child-table without having a parent-key in the parent table.
That means that the constraint is checked on inserts/updates/deletes.

RELY DISABLE NOVALIDATE
ALTER TABLE sales ADD CONSTRAINT sales_time_fk
FOREIGN KEY (time_id) REFERENCES times (time_id)
RELY DISABLE NOVALIDATE;

This constraint is NOT used for data validation. If you know data is correct by running the ETLs than you can use this option.
DWH-Tools can use this info for retrieving information on this constraint. And advanced query-rewriting can be possible for materialized views.

  • No data validation -> no cpu-consumption.
  • No data validation -> inexpensive and no overhead on running dml.

More Infos can be found in the
ORACLE Database Data Warehousing Guide 11g or
ORACLE Database Data Warehousing Guide 12c.