0

I have two databases on two different physical servers. At this time, I want to make relation between some tables in database1 and some tables in database2. I want to know is there anyway for doing that? Note I have both mysql and oracle databases on my two servers and I can use them. Is it possible to make join between to physical databases with mysql or oracle?

hamed
  • 7,939
  • 15
  • 60
  • 114
  • In oracle you can do it using a database link also know as DB_LINK: http://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm and on mysql you can do it: http://stackoverflow.com/a/1566137/460557 but be aware that you can only do SQL operations, you can not create foreing constraints between then. – Jorge Campos Jul 21 '15 at 04:02
  • In oracle you can even create a database link to another database engine like mysql. I don't know if it is possible the other way around with mysql though. – Jorge Campos Jul 21 '15 at 04:03
  • @JorgeCampos Thank you for your replay. But I want to create foreign key constraint. There isn't anyway? – hamed Jul 21 '15 at 04:07
  • You're not going to be able to create a foreign key constraint between an Oracle and a MySQL database. You can't create foreign key constraints between two Oracle databases. You really wouldn't want to have foreign key constraints across servers-- if you could have that, the overhead would be rather significant. – Justin Cave Jul 21 '15 at 04:44
  • @Hamed AFAIK there is no way to create a foreign key cross database. But PT_STAR gave you an alternative that I didn't know of and maybe you can create the same thing between oracle and mysql but unfortunatelly not the other way around. – Jorge Campos Jul 21 '15 at 11:46

1 Answers1

1

Hamed, check this out.

In oracle it is possible to have a foreign key constraint based on a view. So follow these steps:

first: create a view to a table on the remote database. example:

create view test_view_dblink as
select * from some_table@some_dblink;

second: create a foreign key constraint on the view. example:

alter view test_view_dblink
add constraint test_view_dblink_fk foreign key (column_name)
references table_view_in_your_database(column_name) disable;

the "disable" in the constraint definition is very important. in my environment this works perfect!

You can also have a primary key:

alter view test_view_dblink
add constraint test_view_dblink_pk primary key (column_name) disable;
PT_STAR
  • 505
  • 1
  • 4
  • 13