CODEWITHSUNDEEP
databaseoracle-databaseforeign-keysrelational-databaseprimary-key
Mehmood Arbaz
Mehmood Arbaz

Reputation: 295

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.

Upvotes: 7

Views: 15714

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 14

Related Questions