Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Referencing the foreign key of another schema

For my project I am using oracle database where I have to work with 2 different database schema. Consider the following scenario please -

I have a schema A and in it I have a table table_a with a primary key apk

And I have another schema B and in it I have a table table_b with a primary key bpk

If both of these tables are in a same database then I can easily make a primary key - foreign key relationship.

But can I make a primary key - foreign key relation ship (or something like this) between these two columns - A.table_a.apk and B.table_b.pbk.

Thanks in advance.

like image 622
Mehmood Arbaz Avatar asked Feb 10 '23 15:02

Mehmood Arbaz


1 Answers

To create a foreign key that references an object in a different schema, you just need to qualify the object name

ALTER TABLE B.table_b
  ADD CONSTRAINT fk_b_a FOREIGN KEY (apk) REFERENCES a.table_a( apk )

This also requires that the user B has sufficient privileges on A.table_a. The user would need to have the REFERENCES privilege and would, presumably, need to have the SELECT privilege on the table as well.

like image 168
Justin Cave Avatar answered Feb 13 '23 10:02

Justin Cave