Re: Foreign key constraint for array-field?

On Sat, Sep 20, 2008 at 8:38 PM, Dmitry Koterov <dmitry@koterov.ru> wrote:
> Hello.
>
> 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?

Well, it is possible to basically do this with triggers.  However,
ISTM you are doing something that is much easier done with a map
table:

create table a_b_map
( a_id int references a(a_id), b_id int references b(b_id), primary key(a_id, b_id)
);

Also, I would suggest not using columns named 'id' (as in the above
example).  For various reasons, it creates a mess.

merlin