Referencing the foreign key of another schema

10,108

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.

Share:
10,108
Mehmood Arbaz
Author by

Mehmood Arbaz

Updated on June 04, 2022

Comments

  • Mehmood Arbaz
    Mehmood Arbaz about 2 years

    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.