Re: Foreign keys to inherited tables

On 3/20/08, Erik Jones <erik(at)myemma(dot)com> wrote:
> I think he's talking about foreign keys from a "partitioned table",
> i.e. a parent and all of its child tables, to another table. That
> would, at first, sound simple, but scenarios like this make it tricky
> as something to be handled automatically in a simple way:

Well, I was actually talking about foreign keys /to/ a partitioned
table -- table A points to partitioned table B, which has childs C and
D. The foreign key will only be checked in table B, and not tables C
and D.

> Say you have table A that references table B. You then partition
> table A. Say this carries down the references to table B to each
> child of table A. You then partition table B. How do you know, or
> rather how does Postgres know, how to change those foreign keys?
> It's entirely possible that the partitioning scheme on table B doesn't
> match that of table C.
>
> One solution (and, probably the most sane that I can think of) is to
> NOT explicitly carry the foreign keys down to the child tables and,
> instead, to have the actual foreign key checks follow inheritance
> chain. However, with just that most people probably wouldn't want
> that as that could seriously kill performance of even simple write
> queries. Following that up with making foreign key checks "constraint
> exclusion aware" could help there but, at this point, you can probably
> see why a sane implementation of this probably wouldn't be considered
> low hanging fruit.
>
> For practical workarounds, you can use triggers on your child tables
> to implement referential integrity checks customized to your
> particular setup.

Yeah I was thinking about a bunch of triggers too, but was wondering
whether there were any other "elegant" solutions for this. The foreign
keys are actually already guaranteed by my application logic, so I'm
starting to wonder whether this is becoming more trouble to implement
than it's worth.

Too bad this isn't supported by PostgreSQL (yet).

--
Leon Mergen
http://www.solatis.com