Can I create Foreign Keys across Databases?

Question

We have 2 databases - DB1 & DB2.

Can I create a table in DB1 that has a relation with one of the tables in DB2? In other words, can I have a Foreign Key in my table from another database?

I connect to these databases with different users. Any ideas?

Right now, I receive the error:

ORA-00942:Table or view does not exist

Answer 1

No, Oracle does not allow you to create a foreign key constraint that references a table via a database link. You would have to use triggers to enforce the integrity.

Answer 2

One way to deal with this would be to create a materialized view of the master table on the local database, then create the integrity constraint pointing to the MV.

That works. But it can lead to some problems. First, if you ever need to do a complete refresh of the materialized view, you'll need to disable the constraint before doing do. Otherwise, Oracle won't be able to delete the rows in the MV before bringing in the new rows.

Second, you may run into some timing delays. For example say you add a record to the master table on the remote site. Then you want to add a child record to the local table. But the MV is set to refresh daily and that hasn't happened yet. You'll get a foreign key violation, simply because the MV hasn't refreshed.

If you go this route, your safest approach is to set the MV to fast refresh on commit of the master table. That'll mean keeping a DB Link open nearly all the time. And you'll have admin work to do if you ever need to do a complete refresh.

All in all, we've generally found that a trigger is easier. In some cases, we've simply defined the FK in our logical model but implemented it manually by setting up a daily job that will check for violations and alert staff. Of course, we're pretty careful so those alerts are exceedingly rare.