Re: [PATCH] Support for Array ELEMENT Foreign Keys

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> I'm late to this party, so I apologize in advance if this has already
> been considered, but do we actually need a special syntax? Can't we just
> infer that we have one of these when the referring column is an array
> and the referenced column is of the base type of the array?

Yeah, that was suggested before. I for one think it's a seriously bad
idea. It takes away, or at least weakens, a fundamental sanity check
on foreign-key references.

Another point (which is not well handled by my []-syntax idea, I guess)
is that it's not clear that there is one and only one sensible semantics
for the case of an array referencing a scalar. We debated about "all
elements of array must have a match" versus "at least one element of
array must have a match". If we have some special syntax in there then
there's room to change the syntax to select a different semantics,
whereas if we just automatically do something when the column types
are inconsistent, we're not gonna have a lot of wiggle room to support
other behaviors.

This thought also crystallizes something else that had been bothering me,
which is that "ELEMENT" alone is a pretty bad choice of syntax because
it entirely fails to make clear which of these semantics is meant.
I'm tempted to propose that we use

FOREIGN KEY (foo, EACH ELEMENT OF bar) REFERENCES ...

which is certainly more verbose than just "ELEMENT" but I think it
makes it clearer that each array element is required to have a match
separately. If we ever implemented the other behavior it could be
written as "ANY ELEMENT OF".

That doesn't get us any closer to having a working column-constraint
syntax unfortunately, because EACH is not a reserved word either
so "EACH ELEMENT REFERENCES" still isn't gonna work. I'm getting
more willing to give up on having a column-constraint form of this.

regards, tom lane