Re: Arrays and foreign keys

On Fri, 11 Aug 2000, Chris Bitmead wrote:

> Stephan Szabo wrote:
> 
> > 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?
> 
> Probably the only useful use of arrays in conjunction with referential
> integrity is to treat the array as an unordered collection.  
> 
> {1,2,3} -> {1,3,4} -- Is a delete of 2 and an insert of 4.
>                      
> {1,2,3} -> {3,4,1} -- Is a delete of 2 and an insert of 4.
>
> For that reason I'm not sure that it has to be slow. When an array is
> updated find the elements that have changed (according to the above
> definition of changed) and only check on those ones.

Remember, his structure was the array referenced the integer, not the
other way around.  So, if you say, delete one of the integers from the
referenced table you need to find any array element that referenced that
integer in all rows of the referencing table, that's the slow part.