My Remarks!

the Life, the Universe and almost Everything what's Left

How to make a relation from Oracle to another physical database

... exactly this was the question of a user, because he want to join tables between two Oracle databases. Well, the idea for this solution is based upon the fact, that we can have a foreign key constraint on a view.

1. We create a view which points to a table/view on the foreign database, using a database link:

create view test_view_dblink as
select * from some_table@external_oracle_database;

2. We create a foreign key constraint on that view

alter view test_view_dblink
add constraint test_view_dblink_fk foreign key (column_name)
references table_in_local_database(column_name) disable;

The "disable" clause at the end of the statement is the important thing because constraints on views must be disabled.

2b. If we would need a primary key we also can define one:

alter view test_view_dblink
add constraint test_view_dblink_pk primary key (column_name) disable;