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@Yahoo.com #