Скачать или смотреть Solving ORA-02292: Integrity Constraint Issues When Refreshing Materialized Views in Oracle

Discover how to effectively resolve the `ORA-02292: integrity constraint` error associated with refreshing materialized views in Oracle. With a practical example and clear, organized steps, you'll learn how to implement a deferrable foreign key solution.
---
This video is based on the question https://stackoverflow.com/q/76207574/ asked by the user 'user103716' ( https://stackoverflow.com/u/6777650/ ) and on the answer https://stackoverflow.com/a/76207906/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: ORA-02292: integrity constraint, when refreshing materialized view

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding ORA-02292: Integrity Constraint Errors in Oracle

Encountering the ORA-02292: integrity constraint error while refreshing materialized views in Oracle can be frustrating. This issue arises particularly when you have a foreign key linked to a materialized view, which is populated from a parent table. In this article, we will explore why this error occurs and provide a step-by-step workaround to resolve it.

The Problem: Why Does the Error Occur?

In our scenario, we have two tables: a parent table (TEMP_20230509_PARENT) and a child table (TEMP_20230509_CHILD) that references a materialized view (TEMP_20230509_PARENT_MV). The foreign key constraint in the child table points to the ID of the materialized view. When you delete and reinsert a record in the parent table, you might expect the child table to remain unaffected. However, this is not the case due to how Oracle processes the refresh of the materialized view.

Materialized View Behavior: When you initiate a refresh, Oracle may first remove entries from the materialized view before reinserting them. This can lead to a situation where the foreign key constraint is violated if the child table still contains references to the record that has been temporarily removed.

The Solution: Using Deferrable Foreign Keys

One efficient solution to prevent this integrity constraint violation during a refresh is to create your foreign key constraint as INITIALLY DEFERRED DEFERRABLE. This allows Oracle to postpone the enforcement of the foreign key constraint until the end of the transaction. Here’s how to implement this solution:

Step 1: Create Parent Table

Start by creating your parent table with the necessary primary key.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Create Materialized View Log

Next, set up a materialized view log on the parent table that will track changes.

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Create Materialized View

Then, define the materialized view that pulls data from the parent table.

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Create Child Table with Deferrable Foreign Key

Now, create the child table with the foreign key constraint marked as deferrable.

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Insert into Child Table

Add a record to the child table which references the materialized view.

[[See Video to Reveal this Text or Code Snippet]]

Step 6: Insert New Record in Parent

Adding a new record to the parent table should not impact the child table.

[[See Video to Reveal this Text or Code Snippet]]

Step 7: Refresh the Materialized View

A full refresh of the materialized view should now work without errors.

[[See Video to Reveal this Text or Code Snippet]]

Step 8: Delete and Reinsert Record in Parent

If you delete a record from the parent and reinsert it, the previous issues should be resolved.

[[See Video to Reveal this Text or Code Snippet]]

Step 9: Successful Refresh After Deletion

Lastly, execute a full refresh again. This time, it should complete successfully without generating an ORA-02292 error.

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By employing the INITIALLY DEFERRED DEFERRABLE option for your foreign key constraints, you can effectively avoid the ORA-02292: integrity constraint error when refreshing materialized views in Oracle. This approach allows your database to handle transient integrity constraints during the refresh process, ensuring smoother operations and reducing disruptions in your workflow.

If you find this solution helpful, be sure to share it wi