Re: foreign keys for array/period contains relationships

>
> On Mon, Oct 25, 2010 at 15:11, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>
>> Example #4: PK is period, FK is timestamp. FK must be contained in some
>> PK period.
>>
>> CREATE TABLE pk (a period PRIMARY KEY, ...);
>>
>> CREATE TABLE fk (x timestamp REFERENCES pk (a), ...);
>>
>> As above, we can probably arrange the operator knowledge to make these
>> checks. But I think additionally, you'd need an exclusion constraint on
>> the PK side to ensure nonoverlapping arrays/periods so that on
>> update/delete restrict as well as cascading deletes work.
>>
>
> Additional interesting examples involve IP network containment using
>> inet/cidr or ip4/ip4r. There, you'd probably need additional syntax to
>> tell the system explicitly which operators to use.
>>
>
> There are a large number of use-cases for this type of foreign key with
> geometry ( PostGIS ) types as well. Point references Area or Line, Area
> references Area, etc.
>

You may be interested in an experiment I did late last year, where I did a
bit of playing around in the system catalogs to create this kind of
relationship. Turns out that the RI infrastructure stores the oid of the
equality operators it uses in pg_constraint, and after creating a normal
foreign key constraint it can be updated to change these operators. Of
course, Bad Things can probably happen if you violate assumptions that the
RI code depends on. I suspect that the most important one is that a child
row must reference exactly one parent row.

For establishing a point-contained-in-box relationship, the parent-eq-parent
operator is BOX && BOX and the child-eq-parent operator is POINT <@ BOX.
The parent-eq-child operator is BOX @> POINT, which is the commutator of
child-eq-parent. Declaring an exclusion constraint on the parent column
using the && operator guarantees that the <@ operator can only match a
single parent row.

If we're able to teach Postgres about these operator relationships -- that
is, && combined with <@ satisfies the restriction that a child row can only
reference one parent row -- extending the RI creation code to support this
kind of a relationship looks to be fairly straightforward.

(I've attached a small .sql script which demonstrates this for the POINT <@
BOX case.)

-Andrew