Skip to Main Content
  • Questions

  • Create relation between tables in different databases

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: January 24, 2017 - 9:32 am UTC

Last updated: April 11, 2017 - 12:33 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

Is this possible, creating relation between two tables which are stored in Two different Data Bases?

and Chris said...

You mean create a foreign key from DB1 -> DB2?

The answer is no:

create table t (
  x int primary key
);

create table t2 (
  y int
);

alter table t2 add constraint fk foreign key (y) references t@loopback(x);

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


Though a workaround is possible:

- Create a materialized view selecting the table across the database link. This effectively makes the table local
- Create a FK pointing to the MV:

create materialized view mv as
  select * from t@loopback;
 
alter table t2 add constraint fk foreign key (y) references mv(x);


Be very careful before doing this though! You need to ensure that you can keep the MV fresh to stop FK errors. And this increases the complexity of your system, making it harder to manage.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 07, 2017 - 11:51 am UTC

Hi,
i created two tables pk and fk in Scott and HR Schemas respectively.
Pk has Primary Key,FK has Foreign key,points to scott.pk(Granted all to HR on PK).
Pk table has some data, so i tried to insert data into fk table but it was taking too much time so terminated the session, tried to drop the fk foreign key then got ORA-00054 Resource busy, finally i got this error for every operation on this tables. but not getting that errors for others table only for this tables. can you tell me can not we create relation between tables in different schemas?
Connor McDonald
April 08, 2017 - 5:19 am UTC

Yes you can have foreign keys across schemas.

The resource busy is because there is an active transaction on the table.

No Active Transactions

A reader, April 10, 2017 - 9:04 am UTC

There is no any active transactions on those table, but i am getting that Error Why?
Connor McDonald
April 11, 2017 - 12:33 am UTC

"There is no any active transactions on those table"

I think you'd have to *prove* that statement to us
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.