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.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.