Re: referential integrity between elements of an array and another table?

No hesitation. I am just trying to understand the options. This blog seemed
to indicate the functionality existed but it looks like it was never
completed (
https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/
).

I see in this document
https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html that
postgresql uses triggers to implement foreign keys.... so I am probably
just missing the syntactic sugar for arrays. I will try to use a trigger.
Thank you.

On Wed, Apr 19, 2017 at 12:24 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On 04/19/2017 01:13 PM, Henry M wrote:
>
> I was just reading this question on reddit (the text duplicated below). I
> was wondering if there is an approach for handling array foreign key
> references. I am interested in the answer since I started using array
> fields as well. Thank you.
>
>
> ----- below is the message from the reddit poster:
>
>
> https://www.reddit.com/r/PostgreSQL/comments/66a74t/question_referential_integrity_between_elements/
>
> First off, thanks for taking the time. I don't see any "weekly help
> threads" or anything so I hope it's okay I made a post.
>
> *The Problem*
>
> I'm extending our CRM database in order to manage the ISO documentation of
> the company for which I work. We need, as part of ISO, to keep track of
> project development meetings and attendees.
>
> Currently, I have a "meetings" table with an attendee list as an array of
> integers, each element corresponding to the id field in the "person" table.
>
> How do I enforce referential integrity between elements of this array and
> the id field in "person"? All I could find was a proposed change for 9.2 or
> 9.3 that got shot down...
>
> Please help. I don't want to do a join table.
>
> Unless you have thousands of attendees per meeting and everyone in
> multiple meetings per day I would encourage a join table. What's your
> hesitation for doing so? Note I'm an array user myself, so not against
> arrays per se, but there should be a justification for denormalizing
> (depending your definition thereof).
>