Skip site navigation (1) Skip section navigation (2)

Re: Array elem as foreign key?

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Gan Uesli Starling <alias(at)starling(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Array elem as foreign key?
Date: 2005-10-21 06:48:46
Message-ID: 1129877327.28846.9.camel@braydb.bray-healthcare.com (view raw or flat)
Thread:
Lists: 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


In response to

pgsql-novice by date

Next:From: Neil SaundersDate: 2005-10-21 15:47:24
Subject: Database design advice
Previous:From: Galliker ArminDate: 2005-10-20 21:14:43
Subject: store images in a database?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group