How to implement a cross-database foreign key constraint?

Question

Let's say I have two schemas: HR and Orders.

[HR].Employees         [Orders].Entries
--------------         ----------------
Id_Employee    ---->   Employee
Fullname               Id_Entry
Birthday               Description
                       Amount

As you can see, what I'd want is to be able to establish a cross-database foreign key, but when I try this using a database link, I get:

-- From [Orders]
ALTER TABLE Entries
    ADD CONSTRAINT FK_Entries_Employees FOREIGN KEY (Employee)
    REFERENCES Employees@HR;
COMMIT;

ORA-02021: DDL operations are not allowed on a remote database

Is there a way around this? It's a legacy database, so I can't change the existing schema.

For the NHibernate crowd: I would then use this relation to map the NHibernate's domain objects.

Answer 1

As far as I know constraints and referential integrity are only supported within one single database.

If you need to cross the boundaries of the database, you'd have to be creative. Maybe write some triggers checking for data in the other database or enforce these constraints on the application level. But then you may encounter the problem with transaction scope limited to one single database.

Answer 2

One option would be to create a materialized view of Employees on [Orders] and then use that as the parent for the foreign key.

Of course, that has some drawbacks. In particular,

-- you won't be able to do a complete refresh of the materialized view without disabling the foreign key, so it'll have to fast refresh.

-- keys entered into EMPLOYEES won't be available to ENTRIES until the materialized view refresh. If that's critical, you may want to set it to refresh on commit.

Other alternatives are to handle the key enforcement yourself through a trigger or through a post cleanup process. Or convince the DBA's that these schemas can reside on the same database instance.