Re: table inheritance and foreign keys

On Sat, 2003-05-17 at 02:23, Reece Hart wrote:
> James-
>
> Thanks for your advice.  I think I'll opt for the following approach
> instead, but I'm interested to hear of any pitfalls you foresee.
>
> Subtables (for which I wanted to use inherit) will instead contain only
> the subtable-specific columns, and in addition contain a foreign key
> into the parent table.  A view with insert, update, and delete rules can
> emulate the appearance of a full subtable (ie, inherited and
> subtable-specific columns).
>
> The upside I see over triggers is that the referential integrity is
> still on postgresql's back.  The downside is that I end up with more
> tables and views to maintain and an extra join in many cases.

Heh, that's quite clever. I must admit I didn't even consider that.
You're right about it requiring lots of views, but my solution requires
lots of triggers. There's not much difference there, but it's more
visible when you forget to make a view - if I forget a trigger I
probably won't see it until the next audit of the database. I decided to
chase the "real inheritance" angle rather than looking for alternatives
such as this because I figured it was likely to be more future
compatible - I was hoping that either the postgres developers would add
inherited RI functionality or I would get time to implement it myself
(which I started doing). Of course, the RI in place atm is done with
triggers, so a C module to do it, along with some clever code to
actually add the triggers would allow it to live in contribs and not
sacrifice too much convenience.

>
> Again, thanks for your advice.  Do you see any reason that the above
> sketch wouldn't work to emulate foreign keys into inherited tables?

I think it's a good way to proceed with the current version of
postgresql.

James.