Re: table references with partitioning

On Mar 19, 2008, at 12:54 PM, dan chak wrote:

> I'm running into a problem with maintaining referential integrity
> with inheritance-based partitioning.  Imagine a situation where
> partitions are based on time.  Two tables A and B are partitioned,
> and B references A.  If records in B are added some time after
> records in A, the insertion times may cross the partition boundary,
> resulting in A records in last month's partition and B entries in
> this month's partition.  Thus the references need to point at the
> base table, which (at least when selecting from it) appears to have
> all the records.
>
> I've tried two approaches to managing references pointing at a base
> table.  In the first approach, the references are all defined on the
> inheritance base tables and point at other inheritance base tables.
> In this case, anything goes when inserting into child tables.  The
> references don't appear to be checked at all.  Example:

<snip>

> I've also tried adding references to the child tables, with those
> references pointing at the base tables of the referenced relation.
> In this case, an insert that should work doesn't.  True, the values
> aren't physically in the base table, but it's the only express
> what's needed...  Example:

<snip>

> It appears the only way to get referential integrity to flat out
> work as expected is to maintain references between child tables and
> not point at base tables at all.  But then I'm back to square one.
> If a record in b_2 (february) needs to reference a record either in
> a_1 (january) or a_2 (february), based on when A was inserted
> relative to B, then it's not going to work.

Right, as it stands now, foreign keys will not follow inheritance
relationships.  I believe this is in the TODO list although I haven't
heard of anybody actually picking up on it, but then I don't follow -
hackers.  What you can do is create your own foreign key functionality
that will scan both a_1 and a_2 via a trigger.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com