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 <robjsarg...@gmail.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). >
