One way to solve this problem is to create a materialized representation of the main table in the local database, and then create an integrity constraint that points to MV.
It works. But this can lead to some problems. First, if you need to perform a full update of the materialized view, you need to disable the restriction before execution. Otherwise, Oracle will not be able to delete rows in MV until new lines are entered.
Secondly, you may encounter some time delays. For example, let's say you add an entry to the main table on a remote site. Then you want to add the child entry to the local table. But MV is configured to update daily, and this has not happened yet. You will receive a foreign key violation, simply because MV has not been updated.
If you go this route, your safest approach is to install a quick, quick MV update while fixing the main table. This will mean that DB Link support will be open almost all the time. And you will have work with the administrator if you need to do a full update.
In general, we usually found that a trigger is simpler. In some cases, we simply defined FK in our logical model, but implemented it manually by setting a daily work that will check for violations and alert staff. Of course, we are very careful, so these warnings are extremely rare.
Jim hudson
source share