Thread: [GENERAL] referential integrity between elements of an array and another table?
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:Re: [GENERAL] referential integrity between elements of an array andanother table?
From
Rob Sargent
Date:
On 04/19/2017 01:13 PM, Henry M wrote:
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).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:
Re: [GENERAL] referential integrity between elements of an array andanother table?
From
Rob Sargent
Date:
On 04/19/2017 01:13 PM, Henry M wrote:
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).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:
Re: [GENERAL] referential integrity between elements of an array andanother table?
From
Adrian Klaver
Date:
On 04/19/2017 12: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. The only thing I can think of is a faux FK, a INSERT/UPDATE trigger that verifies that the ids in the array are in the person table. > > > ----- 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. > -- Adrian Klaver adrian.klaver@aklaver.com
Re: [GENERAL] referential integrity between elements of an array andanother table?
From
Henry M
Date:
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@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: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).
Re: [GENERAL] referential integrity between elements of an array andanother table?
From
Ken Tanzer
Date:
For me, foreign keys for arrays would be incredibly helpful. For simple checkbox-type options, it's _much_ simpler to just put them in an array, and I work with a UI that can handle the arrays well. I do hope this makes it into Postgresql someday! In the meantime, I've mostly sacrificed the referential integrity in favor of the convenience. Just my two cents.
Cheers,
--
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Re: [GENERAL] referential integrity between elements of an array and another table?
From
Tom Lane
Date:
Henry M <henrymanmail@gmail.com> writes: > 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/ > ). Yeah, there was a patch like that submitted, but we couldn't come to consensus on how the feature should behave. [ digs in archives... ] See these threads: https://www.postgresql.org/message-id/flat/4EB3DF02.4020604%402ndQuadrant.it https://www.postgresql.org/message-id/flat/1343842863.5162.4.camel%40greygoo.devise-it.lan > 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. It's not just a matter of syntactic sugar, there are some pretty basic questions about what the semantics ought to be. We also ran up against implementation/performance problems that we couldn't find passable solutions to. Now, a lot of those problems might not apply to a single-use application that doesn't have to worry about questions like cross-type comparison semantics; but that's why you don't see such a feature in current Postgres. regards, tom lane