Foreign key reference to table in another schema

Question

I tried to create a foreign key on one of my tables, referencing a column of a table in a different schema.

Something like that:

ALTER TABLE my_schema.my_table ADD (   CONSTRAINT my_fk     FOREIGN KEY (my_id)     REFERENCES other_schema.other_table(other_id) ) 

Since I had the necessary grants, this worked fine.

Now I wonder if there are reasons for not referencing tables in a different schema, or anything to be careful about?

Answer 1

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.

Answer 2

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.