Re: Can arrays reference primary keys in CREATE TABLE?

Luis Sousa wrote:
> Jan Wieck wrote:
>> You used a trigger to check something, but unless you have a full set of
>> said triggers and they do the locking as well, you do not have
>> referential integrity.
>>
>>
>> Jan
>>
>>
> I guess you are right.
> I'll have to write a trigger for UPDATE or DELETE. How can I do the lock 
> only on that record ?

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
and loop through the arrays - but it has to select all of them for
update (resulting effectively in a full table lock, bye bye concurrency)
because otherwise it'll see an older snapshot ignoring concurrent
transactions.

What you can do, if you don't want or can't give up the array in the
first place, set up another table that holds the primary key and the
arrays single values in rows. This table is managed by INSERT, UPDATE
and DELETE triggers of the table with the array. Now put the foreign key
constraint on that table instead and you have the desired behaviour with
real 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 #