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