FK to table with non-unique values via a on commit refreshing materialized view

foreign keymaterialized-vieworacleoracle-11greferential-integrity

I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates – dont ask why, not in my control ]

I tried creating a materialized view:

CREATE MATERIALIZED VIEW UniqueView
REFRESH ON COMMIT 
AS SELECT distinct currency from currencies

.. and then creating a FK to UniqueView. This works however, I cannot use ON COMMIT refresh because "distinct" function is used.

I would like the materialized view to be always up to date with the underlying table.

Any other methods of achieving this?
Many thanks in advance!

Best Answer

You can still use GROUP BY.

create table t1 (c1 varchar2(3), c2 number);
insert into t1 values ('EUR', 100);
insert into t1 values ('EUR', 200);
insert into t1 values ('USD', 150);
commit;

select * from t1;
C1          C2
--- ----------
EUR        100
EUR        200
USD        150

create materialized view log on t1 
  with rowid (c1) including new values;

create materialized view v1
refresh fast on commit as
select c1 from t1 group by c1;

select *from v1;
C1
---
EUR
USD

insert into t1 values ('CHF', 180);
commit;

SQL> select * from v1;
C1
---
EUR
USD
CHF
Related Question