Re: Foreign key constraint for array-field?

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.

+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.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828