Foreign key reference to table in another schema Foreign key reference to table in another schema oracle oracle

Foreign key reference to table in another schema


No problem doing this. Schemas really have no impact when establishing foreign key relationships between tables. Just make sure the appropriate people have the permissions necessary for the schemas you intend to use.


If you're in an organization where different people have authority over different schemas, I think it's good practice to give the other schema the ability to disable, or even drop and recreate, your constraint.

For example, they could need to drop or truncate their table and then reload it to handle some (very weird) support issue. Unless you want to get called in the middle of the night, I recommend giving them the ability to temporarily remove your constraint. (I also recommend setting your own alerts so that you'll know if any of your external constraints get disabled or dropped). When you're crossing organizational/schema lines, you want to play well with others. The index that Vincent mentioned is another part of that.


This will work exactly as a foreign key that references a table in its own schema.

As with regular foreign keys, don't forget to index my_id if the parent key is ever updated or if you delete entries from the parent table (unindexed foreign keys can be a source of massive contention and the index is usually useful anyway).