Inheritance and foreign key constraints using postgresql?

Question

In our project, we have a lot of benefits from the PostgreSQL database inheritance function!

We implement the dynamic creation of schemes for different agencies. This structure allows us to solve many security problems that arise directly in the case of bulk tables (without partitioning).

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

Since PostgreSQL has certain limitations (see inheritance warnings ), we are forced to maintain the table structure without restriction.

Is it possible to "simulate", even assuming a relative performance gap, foreign key constraints using triggers and / or checks?

Any suggestions are greatly appreciated! Thank.

Answer 1

The only problematic situation is that you are referencing a table that is shared, a table parent. You can get around this with a parent_idssingle-column shared table id int primary key. You will need to maintain this table using triggers on your child tables, but it is very simple: - insert into parent_ids insert, delete from it when deleting, update it when updating, which will change id.

parent parent_ids. id 2 .

, - .

Answer 2

, .