On Mon, Oct 25, 2010 at 15:11, Peter Eisentraut
<peter_e@gmx.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
