Re: Arrays and foreign keys

Stephan Szabo wrote:

> Actually, it would be:
> * Change foreign key constraint for array -> element to mean element
>    in array,
> since the constraints seem to work on arrays (make two integer
> arrays and reference them and it seems to work in my two minute test).
> 
> The question is whether or not we want to extend the spec in this way.
> It would probably be easy to do, but it's definately an extension, since
> the spec says that the two things should be equal, and I don't generally
> think of element in array as equality.  And, what do we do if neither
> the in operator nor equals is defined between array and element?

Maybe the syntax should be extended to support this concept. Thus
instead of having....


CREATE TABLE table2 (
      pkey             integer NOT NULL,
      arvar              integer[],
 PRIMARY KEY (pkey),
 FOREIGN KEY (arvar) REFERENCES table1(fld1)
);

We instead have....

CREATE TABLE table2 (
      pkey             integer NOT NULL,
      arvar              integer[],
 PRIMARY KEY (pkey),
 FOREIGN KEY (arvar) REFERENCES table1(fld1[])
);

The extra [] meaning that it references a member of fld1, but we don't
know which. That would leave strict equality intact, but still provide
this very useful extension.