Creating Oracle tables with foreign keys that reference primary keys on materialized views

Question

I have several materialized views in Oracle which I can query to get information.

Now I want to create several tables with foreign keys referencing those MVs and to do so, I have already "added" the corresponding primary keys to the MVs (as stated in adding primary key to sql view).

Then, when I execute my SQL create table query, I get an Oracle (ORA-02270) error: no matching unique or primary key for this column-list error at position 0, right at the beginning...

Am I doing something wrong? Is it possible what I am trying to do? If not, how is it usually done?

Answer 1

When there are materialized views referenced by other tables' foreign keys, you have to take note on your views refresh method and how it affects your foreign keys. Two things may prevent you from refreshing your materialized views:

1) The data in the tables referencing your views may reference lines that need to be updated or deleted. In that case you have to fix your data.

2) Your views' refresh method is complete. In complete refresh Oracle deletes all data in your mviews tables and repopulates them by rerunning their queries as you can see in Oracle site documentation - Refresh Types, while in fast refresh only the differences are applied to your mviews tables. Fast refresh is an incremental refresh and it won't work only if your foreign keys aren't respected by your data.

Now if there are mviews that can't be created with fast refresh (what Oracle calls them "Complex queries") then you can alter constraints to these mviews to deferrable as you can see here.

That way even complete refresh will work because Oracle only validates deferrable constraints by the end of current transaction. Therefore, as long as your refresh method is atomic, Oracle will issue an DELETE and than INSERT all rows back, all in one transaction.

In other words, in the next command to refresh your mview keep parameter atomic_refresh as true:

dbms_mview.refresh(LIST=>'MVIEW', METHOD =>'C', ATOMIC_REFRESH => TRUE);

By the way, this parameter's default value is TRUE, so just don't mention it and it will work.

Answer 2

The documentation states that:

View Constraints

Oracle does not enforce view constraints. However, operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce constraints on views through constraints on base tables.

and also:

View constraints are a subset of table constraints and are subject to the following restrictions:

• ...
• View constraints are supported only in DISABLE NOVALIDATE mode. You cannot specify any other mode. You must specify the keyword DISABLE when you declare the view constraint. You need not specify NOVALIDATE explicitly, as it is the default.
• ...

---

In practice, the above means that although constrains on views can be created, they are blocked and do not work. So as if they were not at all.

---

Apart from this, think for a moment what sense it would have foreign key constrainst created on tables, that would refer to a materialized view:

• tables are always "online" and have always "fresh" data
• materialized views can contain stale data

Imagine this case: You insert a record X into some table. This record is not visible in the materialized view yet, because the view is not refreshed at this moment. Then you try to insert record X into another table that has a foreign key constraint pointing to that materialized view. What the database should do ? Should the database reject the insert statement (since for now X is not visible yet in the view and the foreign key exists) ? If yes, then what about data integrity ? Mayby should it block amd wait until the view is refreshed ? Should it force the view to start refreshing or not in such a case?

As you can see, such a case involves many questions and difficult problems in the implementation, so Oracle simply does not allow for constrains on views.