On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql@hjp.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@hjp.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.