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!