Re: Foreign key constraint for array-field?

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. 

Implementation would be fairly straightforward. ri_triggers currently
assumes a non-array value is being checked, but that could be changed to
IN(array). Multi-column keys with arrays sound confusing though.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support