Need to check each element of an array satisfies a foreign key constraint
Hi:
I have a column in a table which is a csv of values and I need to make sure
each element of the csv = the PK of that same table.
create table projects (
project varchar primary key,
children_csv varchar );
insert into projects (project,children_csv) values
('prj1',null),
('prj2',null),
('prj3','prj1,prj2');
I need to make sure that the elements of 'prj1,prj2' are both valid
projects.
I'm thinking the csv should be split into an array (regexp_split_to_array)
but the constraint needs to somehow iterate over each element to check that
they are all valid.
I suppose I could write a stored procedure to do this and call it in a
check constraint. But I was wondering if there is something more elegant.
Thanks in Advance !
On Tuesday, July 13, 2021, David Gauthier <davegauthierpg@gmail.com> wrote:
I suppose I could write a stored procedure to do this and call it in a
check constraint. But I was wondering if there is something more elegant.
You cannot use a check constraint here as the behavior is not immutable.
You can use a trigger function though. Or normalize the table and use the
built-it foreign key triggers.
David J.
Ok, thanks.
I was looking for, but didn't find, something like...
each_element_of(regexp_split_to_array(children_csv)) references
projects(project);
Of course the "each_element_of" is my creation here :-)
On Tue, Jul 13, 2021 at 10:07 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Tuesday, July 13, 2021, David Gauthier <davegauthierpg@gmail.com>
wrote:I suppose I could write a stored procedure to do this and call it in a
check constraint. But I was wondering if there is something more elegant.You cannot use a check constraint here as the behavior is not immutable.
You can use a trigger function though. Or normalize the table and use the
built-it foreign key triggers.David J.
On Tue, Jul 13, 2021 at 7:47 AM David Gauthier <davegauthierpg@gmail.com>
wrote:
Ok, thanks.
I was looking for, but didn't find, something like...
each_element_of(regexp_split_to_array(children_csv)) references
projects(project);
Of course the "each_element_of" is my creation here :-)
Several years ago, I had hopes for this, and still do. It would be great
for my use cases. From what I remember, it was "complicated." There was a
patch proposed at least as far back as 9.3 for this[1]. Googling for
this now though, I see there was some activity on it this year[2]. Does
anyone know the status of this, or if/when this is likely to show up in
Postgresql? Thanks!
Ken
1.
https://www.2ndquadrant.com/en/blog/postgresql-9-3-development-array-element-foreign-keys/
2.
https://www.postgresql-archive.org/GSoC-2017-Foreign-Key-Arrays-td5962835i140.html
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/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.
On Tue, 2021-07-13 at 10:00 -0400, David Gauthier wrote:
I have a column in a table which is a csv of values and I need to make sure each element of the csv = the PK of that same table.
This won't work, and it is broken by design.
Change your data model to adhere to the first normal form, and the
exercise will be simple.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com