How to implement cross-database foreign key constraint?

Question

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

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

As you can see, I want to be able to set the cross-database foreign key, but when I try to use this 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 any way around this? This is an outdated database, so I cannot modify the existing schema.

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

Answer 1

One option is to create a materialized Employees view in [Orders], and then use it as the parent for the foreign key.

Of course, this has some disadvantages. In particular,

- You will not be able to perform a complete update of the materialized view without disconnecting the foreign key, so it needs to be updated quickly.

- the keys entered in the EMPLOYEES will not be available for RECORDING until the materialized representation is updated. If this is a critical value, you can configure it to update when committed.

Other alternatives are to control the forced execution of itself through a trigger or through the mail cleaning process. Or convince the DBA that these schemas can be in the same database instance.

Answer 2

As far as I know, restrictions and referential integrity are supported in only one database.

If you need to cross the boundaries of a database, you need to be creative. Perhaps write some triggers that validate data in another database or apply these restrictions at the application level. But then you may run into a problem with the transaction scope limited to one separate database.