Re: Can arrays reference primary keys in CREATE TABLE?

Stephan Szabo wrote:
> On Fri, 23 May 2003, Oleg Bartunov wrote:
>>On Thu, 22 May 2003, Jan Wieck wrote:
>>>That exactly is the problem with arrays here. You can't index an array
>>>in a way so that you can effectively select all rows who's array
>>>contains a particular value. Your trigger can only select all of them
>>
>>there is contrib/intarray which could be used for indexed access to
>>array of integers. In principle, it should be not difficult to
>>generalize this module to support many types.
>
>
> Yeah, that should get around locking all the rows. It will still mean
> that deadlocks are even more likely to occur with such tables than with
> the normal constraint conditions however (locking the row containing one
> element we're looking for effectively locks for all of those values).
>
> As a side note, there are some other issues with making the constraint
> work for non-no action constraints. I'm not sure if SET NULL or SET
> DEFAULT can be meaningfully done at all with the current array support,
> nor exactly what CASCADE means for delete (does it remove the array
> element or the entire row).

Foreign keys have probably not been designed with a multi-dimensional
design in mind. Now we're talking about an array referencing, what about
arrays of arrays?

While some cases sure can be implemented or simulated, I share Stepahn's
concerns about the meaning of referential actions like ON DELETE CASCADE.

And even if we can solve this one today, tomorrow someone will ask for
an implementation where every even indexed element of the array
references table A and every odd one references table B (except it is a
multiple of 7 because those are used as flags with some totally
different meaning), and of course ON DELETE CASCADE means "remove the
element from the array" so that all following ones flip their
referencing table ... and the multiples of 7's have to leapfrog :-)

If Oracle implements it first, I might change my mind.

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 #