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.

The key word as I understood would be "Enforcing Foreign Keys Programmatically in PostgreSQL" — Igor
– Igor, Commented Sep 9, 2010 at 7:12

Tometzky · Accepted Answer · 2010-09-10 15:48:01Z

4

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.

answered Sep 10, 2010 at 15:48

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Over a year ago

Good hint! Thank you for sharing.

Magnus Hagander · Accepted Answer · 2010-09-09 08:36:13Z

0

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

answered Sep 9, 2010 at 8:36

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

1 Comment

Igor Over a year ago

agree, but to guarantee that your triggers behave in an appropriate manner would be quite a hard job ...

Collectives™ on Stack Overflow

Inheritance and foreign key constraints with postgresql?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related