Обсуждение: Array of foreign key

On 22 December 2017 20:02:43 CET, Thomas Poty <thomas.poty@gmail.com> wrote:
>Hello all,
>
>I use postgresql 9.5 and I am looking for way to have a column which is
>an
>array of a foreign key. If what I read is correct it is currently not
>possible... Have you any idea how to implement this feature in a safe
>way

Normalisation?

Can you explain that a bit more, what's the use-case?


Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company

>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.

Andreas


--
2ndQuadrant - The PostgreSQL Support Company

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/>

On 2017-12-23 12:24:07 -0500, Melvin Davidson wrote:
> On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> >...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".

If you do not understand something, please ask. Don't claim that other
people "don't understand how X works" just because you don't know what
they are talking about. Also, please pay a bit of attention who you
are replying to. I am not the OP. I just understand what he wants (or at
least I think I do).

> So for the sake of CLARIDICATION, would you please
> 1, State the version of PostgreSQL
> 2. State the O/S

Why should I? You didn't state the OS and PostgreSQL version you use
either. And I don't think you should, as it is irrelevant for the
discussion.

> 3. Provide an example of an FK "array" that you are concerned with.

I think the OP wants something like this:

create table features (
    id serial primary key,
    name varchar not null
);

create table products (
    id serial primary key,
    name varchar not null,
    array_of_features int[]
        references [] features(id) -- XXX - invented syntax
);

where products.array_of_features can only contain integers from
features.id. This doesn't currently (as of 10.1) work.
As I wrote, you could use triggers, but there is no way to declare that
foreign key directly.

If you want foreign keys, you have to use an intersection table:

create table features (
    id serial primary key,
    name varchar not null
);

create table products (
    id serial primary key,
    name varchar not null
);

create table product_features (
    product references products(id),
    feature references features(id)
);

But that has a relatively high overhead both in storage and for queries.

I can understand why the OP wants that. I could have used something like
this in the past, too.

        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/>

On 2017-12-23 13:14:50 -0500, Melvin Davidson wrote:
> On Sat, Dec 23, 2017 at 12:49 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>
>     I thought the OP was asking for this feature ("Support for Array ELEMENT
>     Foreign Keys"):
>
>     https://www.postgresql.org/message-id/flat/1343842863.
>     5162.4.camel@greygoo.devise-it.lan#1343842863.5162.4.
>     camel@greygoo.devise-it.lan 
>
>     which would be super-handy, but doesn't actually exist.  You can enforce it
>     yourself with a trigger on both tables, but I still hope this someday gets
>     included into Postgres!
>
> Actually, I think the op may be referring to a MULTI COLUMN FK array

I see no reason to assume that. He explicitely asked about an array of
foreign keys, and there is no array here.

        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/>

On 2017-12-23 14:40:13 -0500, Melvin Davidson wrote:
> On Sat, Dec 23, 2017 at 2:27 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> >If you do not understand something, please ask.
>
> Your response is inappropriate and offensive.

I apologize for my tone. I should have calmed down before replying.

I do not apologize for the substance, though. I think it is entirely
appropriate to point out inappropriate and offensive behaviour, and I
think it is more helpful to point out the specific aspects that one
finds inappropriate and offensive than just making a blanket claim.

To repeat:

* I think it is offensive to claim that somebody doesn't understand some
  basic aspect of RDBMSs unless that person has written something which
  is clearly false. This is a list about an RDBMS and one should assume
  that most of the people here (or at least the regular contributors)
  know at least the basics. Since you were apparently aware that you
  didn't understand what I was writing about, it would have been
  appropriate to consider that you might have misunderstood whatever you
  thought I had written about foreign keys.

* I think it is impolite not to take the 2 seconds to check who you are
  replying to. I understand that in a long thread it is easy to lose
  track of who started it and who wrote what, but this thread wasn't
  that long.

* I think it is inappropriate to ask some random participant in the
  middle of a discussion what OS and PostgreSQL version they are
  running, unless they have related some specific observation about
  their system. Even if I had not been a random participant, but the OP,
  I would have considered the question inappropriate, since at that
  point in the discussion it should have been clear that none of the
  topics (arrays, enums, foreign keys, ...) is dependent on the exact
  PostgreSQL version, much less the OS.


> I specifically asked for clarification because the op stated "column
> which is an array of a foreign key" but gave no details.
>
> >Don't claim that other people "don't understand how X works"
> Again, from the op's reference to using constraint's and triggers, it seemed
> like the op did not fully understand the FK's would do the same.

AFAICS the OP didn't mention triggers at all. Ken and I mentioned them
as a way to simulate what the OP wanted.


> Also, it is not necessary to state which version I PostgreSQL I use, because
> that is irrelevant.

It is also irrelevant what version of PostgreSQL I use (or what version
Ken or Andreas use). The version the OP uses might have been mildly
interesting at the start of the discussion.

        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/>