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:
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.
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).
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).
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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).
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,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
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:
/messages/by-id/4EB3DF02.4020604@2ndQuadrant.it
/messages/by-id/1343842863.5162.4.camel@greygoo.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general