Currently, foreign keys only work with the = operator (the name might be
different, but it needs to behave like equality). I'm thinking there
are other scenarios that could be useful, for example with arrays and
range types.
Example #1: Foreign key side is an array, every member must match some
PK.
CREATE TABLE pk (a int PRIMARKY KEY, ...);
CREATE TABLE fk (x int[] REFERENCES pk (a), ...);
Example #2: Foreign key side as a (hypothetical) period type, PK is a
timestamp. Every FK period must contain a PK timestamp.
CREATE TABLE pk (a timestamp PRIMARY KEY, ...);
CREATE TABLE fk (x period/range of timestamp REFERENCES pk (a), ...);
Implementing the foreign key side of this merely requires the system to
have some knowledge of the required "contains" operator, which it does
in the array case, and something can surely be arranged for the range
case. The problem is you can't do cascading updates or deletes, but you
could do on update/delete restrict, which is still useful.
It get's more interesting when the "container" type is the primary key:
Example #3: PK is array, FK is element type. FK must be element of some
PK array.
CREATE TABLE pk (a int[] PRIMARY KEY, ...);
CREATE TABLE fk (x int REFERENCES pk (a), ...);
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.
Now I originally arrived at this issue via Example #1, but it appeared
to me that with the ongoing work on range types, Example #4 would be a
very eminent use case.
Is this sort of thing feasible? Has anyone done more research into the
necessary details?