Andrew Dunstan <and...@dunslane.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers