This discussion is archived
2 Replies Latest reply: Jan 21, 2013 1:33 AM by user7029403 RSS

foreign key-like constraint to a materialized view?

user7029403 Newbie
Currently Being Moderated
Hello.

I have a materialized view which joins in a single column 'A' two different columns from different tables from a remote database.

I also have a table who has a column 'B' which values must exist in the column 'A' of the materialized view.

So it is like defining a foreign key constraint from a table to a view, but that is not possible.

Is there a way to do that?

I have seen some triggers that check the existence of the value in 'A' before inserting in 'B', but this solution fails when some value is modified or deleted in 'A'

Any suggestions?

Thank you very much.

Edited by: user7029403 on 18-ene-2013 3:57
  • 1. Re: foreign key-like constraint to a materialized view?
    JohnWatson Guru
    Currently Being Moderated
    There is no problem defining a primary key on a materialized view and then referencing it with a foreign key. This works for me:
    create database link l1 connect to scott identified by tiger using 'orcl';
    create materialized view mv1 as select deptno||dname as a from dept@l1;
    alter table mv1 add constraint mv1pk primary key (a);
    alter table dept add (fkcol as (deptno||dname));
    alter table dept add constraint deptfk foreign key (fkcol) references mv1;
    I'm sure there is a more elegant solution than using a virtual column, that just seemed the quickest way of getting around concatenating two columns into one
  • 2. Re: foreign key-like constraint to a materialized view?
    user7029403 Newbie
    Currently Being Moderated
    Ok, It was just that SQL Developer does not allow doing this with the graphical interface, at least in my outdated version.
    Thank you very much.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points