0

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!

  • Do you mean the parent table has duplicate rows, or the child/FK table? – Oreo Feb 13 '18 at 16:55
  • Why not add a currencies table? – ypercubeᵀᴹ Feb 13 '18 at 17:21
  • I think what @ypercubeᵀᴹ meant is you should normalize your database – Giacomo Garabello Feb 13 '18 at 17:35
  • @Oreo, sorry, yes, the parent has duplicates. There is a reason for it, suffices to say that this is how it will stay and I have no control over that. – Greg Balajewicz Feb 13 '18 at 17:46
  • Why not create a new table with just the distinct values to use as a foreign key so you can have more normalized data? – Joe W Feb 13 '18 at 18:14
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.