Re: [HACKERS] Foreign key constraint for array-field?

[Decibel!]

On Sep 21, 2008, at 4:18 AM, Simon Riggs wrote:

On Sun, 2008-09-21 at 04:38 +0400, Dmitry Koterov wrote:

Is it possible to create a foreign key constraint for ALL elements of
an array field?

CREATE TABLE a(id INTEGER);
CREATE TABLE b(id INTEGER, a_ids INTEGER[]);

Field b.a_ids contains a list of ID's of "a" table. I want to ensure
that each element in b.a_ids exists in a in any time. Is it possible
to create an automatic foreign key?

No, its not possible. Need a trigger.

I think we should support it though. If we extend the relational model
with arrays then it would be sensible if we support this aspect as
well.

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828

+1. And for everyone who immediately jumped to "NORMALIZE!" as the answer, consider that that means a bare minimum of 24 bytes overhead per item that would go into the array. It's not hard at all for that overhead to become massive.

smime.p7s
Description: S/MIME cryptographic signature