Inheritance and foreign key constraints with postgresql?

Question

In our project we benefit a lot from the inheritance feature of the PostgreSQL database!

We implement the dynamical creation of schemes for different agencies. This structure permits us to solve a lot of security issues which occurs immediately in the case of bulk tables (without partitioning).

The only problem we encountered is to guarantee the database integrity, which usually (in the sense of the structure without inheritance) is realised by the foreign key constraints.

Since the PostgreSQL has certain limitations (see inheritance caveats) we're forced to maintain the tables structure without the constraints.

Is there any possibility to 'simulate', even supposing the relative performance decay, the foreign keys constraints by means of triggers and/or checks?

Any suggestions are very appreciated! Thank you.

Answer 1

In most cases, it should be possible to write regular triggers on the tables that verifies the relationships when data is modified.