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
shouldreally 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 #