Inheritance and foreign key constraints with postgresql?

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 encount开发者_如何学Goered 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.

The only problematic situation is that you reference from a table, which is shared, to a parent table. You can work around this with the shared table parent_ids with one column id int primary key. You'll have to maintain this table using triggers on your child tables, but it is very simple — insert to parent_ids on insert, delete from it on delete, update it on update, which changes id.

Then instead of referencing your parent table you'll reference this parent_ids table. This would ensure reference integrity and that you'll not have conflicting id in 2 child tables.

It would leak used ids to any user, but will not allow access to any other data.

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