Re: Array elem as foreign key? - Mailing list pgsql-novice

From Oliver Elphick
Subject Re: Array elem as foreign key?
Date
Msg-id 1129877327.28846.9.camel@braydb.bray-healthcare.com
Whole thread Raw
In response to Array elem as foreign key?  ("Gan Uesli Starling" <alias@starling.us>)
List pgsql-novice
On Thu, 2005-10-20 at 16:00 -0400, Gan Uesli Starling wrote:
>
> ...and I'd like each elem of computers.sofwares
> to reference softwares.id (or be NULL) but my
> several attempts to date have been rejected.
>
> Is there a way to do this? If so, how?

Not directly.

You could write a trigger function that tests each element of the array
on update or insert to "computers".  You would also need to write a
trigger for "softwares" to do the check for cascading updates or
deletes.  Since you cannot make a suitable index on an array, it is
going to have to scan every record in "computers" and will therefore be
slow.

Is an array the right way to do this?  Perhaps you should normalise the
data further and create a third table which holds a single combination
in each row.  Then you could use normal foreign key referencing.  If you
want an array for other purposes, you could create a view on the tables
to recombine them into an array.

Oliver Elphick


pgsql-novice by date:

Previous
From: "Gan Uesli Starling"
Date:
Subject: Array elem as foreign key?
Next
From: Neil Saunders
Date:
Subject: Database design advice