Monday, February 20, 2012

Temporary Disabling the Foreign Keys in Oracle


Foreign Keys are the most widely used constraint types in OLTP systems, since they ensures that a value of the referencing column cannot contain a value that does not exist in the corresponding column of the referenced table. Foreign Keys do not guarantee absolute data quality: rather, they guarantee... referential integrity, which is a good starting point for obtaining a good level of data quality and reliability.

OLTP systems are always accessed in a "transactional" way by multiple users: each data entry is inserted row by row, and should therefore be controlled  one by one, togheter with the help of the other database constraints (or the application level filters, if the system is ill-designed).

In DWH environments, however, things are different. First, the data integrity is - or should be- guaranteed by the automatic ETL process; exceptions, error or any data inconsistency has to be automatically managed - and reported - direct at etl level. Sometimes one of the goal of the ETL process is also to find data anomalies and problems: this can be part of the DWH structure itself, for example with the use of an Audit Dimension or Fact.

Second, the presence of explicit foreign keys (logically we ALWAYS have referencial integrity between facts and dimensions) load performance is brutally degraded: a cross-table check is triggered for every insert/update/delete statement, resulting into an unacceptable overhead.

Third, during the developement of a DWH System operations like table truncating and changing are frequent, thus causing headaches when making DML or DDL changes.

If you are not the Project Manager, and you cannot decide to drop -once for all- the explicit and annoying foreign keys from the database, you can temporary disable them by using a simple procedure similar to the one contained in the following anonymous block:


SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('disabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/


Similarly, to re-enable the foreign keys:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN FOR i IN (SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND CONSTRAINT_TYPE='R') LOOP v_stmt := 'ALTER TABLE ' || i.TABLE_NAME || ' ENABLE CONSTRAINT ' || i.CONSTRAINT_NAME; EXECUTE IMMEDIATE v_stmt; dbms_output.put_line('enabled: ' || i.CONSTRAINT_NAME || ' from table: ' ||  i.TABLE_NAME); END LOOP;END;/

As always, do not forget the backslash "/" at the end of the anonymous block.

No comments:

Post a Comment