Re: Array elem as foreign key?

Поиск
Список
Период
Сортировка
От Oliver Elphick
Тема Re: Array elem as foreign key?
Дата
Msg-id 1129877327.28846.9.camel@braydb.bray-healthcare.com
обсуждение исходный текст
Ответ на Array elem as foreign key?  ("Gan Uesli Starling" <alias@starling.us>)
Список 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 по дате отправления:

Предыдущее
От: "Gan Uesli Starling"
Дата:
Сообщение: Array elem as foreign key?
Следующее
От: Neil Saunders
Дата:
Сообщение: Database design advice