referential integrity between elements of an array and another table?

From: Henry M <henrymanmail(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: referential integrity between elements of an array and another table?
Date: 2017-04-19 19:13:49
Message-ID: CAJVHaguJLHq=d0WM=qg1byqB_mozgdbF+fFH67TdOT207Z-GFA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2017-04-19 19:24:03 Re: referential integrity between elements of an array and another table?
Previous Message Rj Ewing 2017-04-19 18:57:26 Re: full text search on hstore or json with materialized view?