Re: Arrays and foreign keys

Kaare Rasmussen wrote:
> > Well, the two types aren't the same (one is an integer the
> > other an integer array,) so I wouldn't expect it to work. Note:
>
> Eh, I could figure that out myself. What I'm asking for is if there is a way to
> combine arrays with foreign keys?
>
> I believe the answer for now is 'no', but did like to get it confirmed, and
> also draw attention to this if someone wants to make it.
>
> > * Make sure that types used in foreign key constraints
> > are comparable.
>
> And maybe
> * Add foreign key constraint for arrays

The major problem isn't that we do not have a comparision
operator for int4 vs. _int4. The bigger one is that there is
no easy way to build an index on them, and that there is no
way to define what a referential action should really do in
the case of cascaded operations.

For a primary key containing an array, the values of all
array elements of all rows must be unique and NOT NULL. So
there must be a unique index on the elements, the array
itself cannot be NULL, no element of the array can be NULL
and there must be at least one element.

And for a foreign key containing an array, what to do when ON
DELETE CASCADE is requested? DELETE the FK row? Remove the
element from the array? DELETE the row then when the array
get's empty or not?

Are these questions answered by the standard? If not, do we
want to answer them ourself and take the risk the standard
someday answers them different?

For the meantime, I suggest normalize your schema if you want
referential integrity.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #