Re: foreign keys for array/period contains relationships

On Mon, 2010-10-25 at 22:11 +0300, Peter Eisentraut wrote:
> 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.

I agree completely. I had not previously considered that arrays could
benefit from this idea as well as range types. Mentally, I had already
been calling them "foreign range keys" ;)

> 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.

Why can't you do cascading updates/deletes?

> Is this sort of thing feasible?  Has anyone done more research into the
> necessary details?

Yes, I think so. #3 and #4 are very feasible. #1 and #2 are, as well,
unless I'm missing something.

Regards,Jeff Davis