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

One reason this can cause problems is you need to be careful to delete things in the right order. This can be good or bad depending on how important it is to never have orphans in your tables.

Answer 2

No problems doing this. Schema's really have no impact when establishing foreign key relationships between tables. Just make sure the appropriate people have the permissions necessary for the schema's you intend to use.

Answer 3

Hi Peter,

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).

Answer 4

The only thing I ran into was making sure the permission existed on the other schema. The usual stuff - if those permission(s) disappear for whatever reason, you'll hear about it.

Answer 5

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.