Re: Array of foreign key

Good evening all, 

I have just see all the messages. Thanks for that. 

First, Peter Holzer has exactly understooden my need. 

I am à bit disappointed this feature is not implemented. It would be great. 

Then, I know enum is probably not the best choice but it is historic  in our database. Like I said previously,  We are working on the first of two steps migration and we will probably not use them after de second phase of migration. You also have to know make this changes needs a lot of tests to be sure the results will be correct and expected.! For our company it is not so easy... 

After that, we are running on pgsql 9.5 and centos 7.x.

Finally,  keep in mind we are here to help each others. 

 

Thanks for your help 

Thomas 

Le 23 déc. 2017 20:25, "Peter J. Holzer" <hjp-pgsql@hjp.at> a écrit :

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