I am really excited about foreign schemas being available in 9.5 . I was testing this feature out and ran into a situation.
Issue:
I have a prod database called db1. I have two schemas, schema1 and schema2. I have two tables schema1.table1 and schema2.table2 . In my current production database a column within schema2.table2 has a foreign key constraint to schema1.table1 . If a record is deleted from schema1.table1 then the corresponding rows are deleted from schema2.table2 .
So I set up a new database on a different server called db2. I then set up a foreign schema on db2 that pointed to schema1 on db1. Then I loaded schema2 directly onto db2 in the hopes that all would work great.
Then I got an error message that said, ‘ERROR: referenced relation “table1" is not a table’. I now am assuming I cannot have foreign keys pointing to tables within foreign schemas.
I guess that makes sense because if db1 was not available then db2 would start deleting data. Is that correct?
Is there a new way to handle foreign keys when using foreign tables?
Lance