Re: Array elem as foreign key?

Howdy,

I have two tables, thus (sans non-relevent cols)....

-- Associate to a computer in the 'computers' table.
CREATE TABLE softwares (
id SERIAL PRIMARY KEY,
-- more cols after
);

CREATE TABLE computers (
--some cols before
softwares INTEGER[10],
--more cols after
);
COMMENT ON COLUMN computers.softwares IS '0th elem = OS. 1st-Nth = Any SW';

...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?

TIA,

--
Mistera Sturno - Rarest Extinct Bird

<(+)__ Gan Uesli Starling
((__/)=- Kalamazoo, MI, USA
`||`
++ http://starling.us

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