Re: Array of foreign key

On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2017-12-22 22:05:18 +0100, Andreas Kretschmer wrote:
> >
> > >Please DO NOT use EMUMs. That is old. They are hard to maintain. I
> > >also know from experience that MySql does not check integrity of
> > >enums. It's possible you can have data in a MySql table column that
> > >is not valid for current enum constraint on that column. EG: When
> > >porting, the enum for a columm (VALID) was "yes, no", but I found a
> > >few that had "maybe" as data.
> > >
> > >The way to go is to simply implement FOREIGN KEYs. Much easier to
> > >maintain in PostgreSQL.
> >
> > Full ack.
>
> But that brings us back to the original question: Is there a way to
> enforce foreign key constraints on the members of an array?
>
> At insert time you can check with a trigger of course, and maybe there
> is a way to do it in a check constraint. But that doesn't prevent you
> from removing a row from the target table.
>
> The only way I can think of is with an intersection table.
>
> hp
>
> --
> _ | Peter J. Holzer | we build much bigger, better disasters now
> |_|_) | | because we have much more sophisticated
> | | | hjp(at)hjp(dot)at | management tools.
> __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
>

>...Is there a way to
>enforce foreign key constraints on the members of an array?
>At insert time you can check with a trigger of course, and maybe there
>is a way to do it in a check constraint

I don't think you understand how Foreign Key constraints work in PostgreSQL.
PostgreSQL will prevent any insert where the value of a column is not
within the FK table.
So you DO NOT need a check constraint or trigger.

What I do not understand is your reference to a FK "array".
So for the sake of CLARIDICATION, would you please
1, State the version of PostgreSQL
2. State the O/S
3. Provide an example of an FK "array" that you are concerned with.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.