From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | dmitry(at)koterov(dot)ru |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Foreign key constraint for array-field? |
Date: | 2008-09-21 02:04:14 |
Message-ID: | b42b73150809201904y2dd918e7rda2e4546d2cb64d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sat, Sep 20, 2008 at 8:38 PM, Dmitry Koterov <dmitry(at)koterov(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-09-21 02:09:10 | Re: [patch] fix dblink security hole |
Previous Message | Joe Conway | 2008-09-21 01:55:20 | Re: [patch] fix dblink security hole |