Re: Array of foreign key - Mailing list pgsql-general

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@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>