Re: Arrays and foreign keys - Mailing list pgsql-hackers

On Fri, 11 Aug 2000, Chris Bitmead wrote:

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

Actually, it's the other way around right, arvar is the array, fld1 is
just an integer, so I'd guess
FOREIGN KEY (arvar[]) REFERENCES table1(fld1) 
would be it.

There are the issues of the referential integrity actions.  If I were
to hazard a guess at the behavior one would expect from this, I'd guess...

ON UPDATE CASCADE - The particular referencing element changes.
ON UPDATE SET NULL - The particular referencing element is set null
ON UPDATE SET DEFAULT - For now the same as set null since i don't think                       array elements can
default
ON UPDATE NO ACTION|RESTRICT - disallow changing of the value if there                              exists an array
elementreference
 
ON DELETE CASCADE - Remove referencing element, drop row if the array                   is emptied
ON DELETE ... - Pretty much as on update.

But (and this is a really big but) -- This is going to be slow as hell,
and perhaps slower than that, since for any update or delete, you would
have to go through every row on the other table doing the array in until
we can get an index on all the elements in all of the arrays.

Then there are other problematic issues like:
{1,2,3} -> {1,3,4} -- Is this a delete of 2 and an insert of 4 or                     two updates?
{1,2,3} -> {3,4,1} -- What about this one?

---
This of course brings up, well, what about an element that wants to
reference an array, or what about arrays that you want to say, this array
must be a subset of the referenced array, but we can get into that
later... :)