Thread: Foreign key constraint for array-field?
Hello. Is it possible to create a foreign key constraint for ALL elements of an array field? CREATE TABLE a(id INTEGER); CREATE TABLE b(id INTEGER, a_ids INTEGER[]); Field b.a_ids contains a list of ID's of "a" table. I want to ensure that each element in b.a_ids exists in a in any time. Is it possible to create an automatic foreign key? According to http://www.postgresql.org/docs/current/static/catalog-pg-constraint.html , seems to me it is possible if I create a custom entry in pg_constraint with my custom conpfeqop, conppeqop and conffeqop fields. Am I right?
On Sat, Sep 20, 2008 at 8:38 PM, Dmitry Koterov <dmitry@koterov.ru> wrote: > Hello. > > Is it possible to create a foreign key constraint for ALL elements of > an array field? > > CREATE TABLE a(id INTEGER); > CREATE TABLE b(id INTEGER, a_ids INTEGER[]); > > Field b.a_ids contains a list of ID's of "a" table. I want to ensure > that each element in b.a_ids exists in a in any time. Is it possible > to create an automatic foreign key? Well, it is possible to basically do this with triggers. However, ISTM you are doing something that is much easier done with a map table: create table a_b_map ( a_id int references a(a_id), b_id int references b(b_id), primary key(a_id, b_id) ); Also, I would suggest not using columns named 'id' (as in the above example). For various reasons, it creates a mess. merlin
On Sun, Sep 21, 2008 at 04:38:56AM +0400, Dmitry Koterov wrote: > Hello. > > Is it possible to create a foreign key constraint for ALL elements of > an array field? Whether it's possible or not--it probably is--it's a very bad idea. Just normalize :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote: > On Sun, Sep 21, 2008 at 04:38:56AM +0400, Dmitry Koterov wrote: >> Hello. >> >> Is it possible to create a foreign key constraint for ALL elements of >> an array field? > > Whether it's possible or not--it probably is--it's a very bad idea. > Just normalize :) +1 > > Cheers, > David.
On Sun, 2008-09-21 at 04:38 +0400, Dmitry Koterov wrote: > Is it possible to create a foreign key constraint for ALL elements of > an array field? > > CREATE TABLE a(id INTEGER); > CREATE TABLE b(id INTEGER, a_ids INTEGER[]); > > Field b.a_ids contains a list of ID's of "a" table. I want to ensure > that each element in b.a_ids exists in a in any time. Is it possible > to create an automatic foreign key? No, its not possible. Need a trigger. I think we should support it though. If we extend the relational model with arrays then it would be sensible if we support this aspect as well. Implementation would be fairly straightforward. ri_triggers currently assumes a non-array value is being checked, but that could be changed to IN(array). Multi-column keys with arrays sound confusing though. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Normalization is not a panacea here. Sometimes such normalization creates too much overeat and a lot of additional code (especially if there are a lot of such dependencies). Array support in Postgres is quite handy; in my practive, moving from a_b_map to arrays economizes hundreds of lines of stored procedure and calling application code. Triggers are not very helpful here, because it is too boringly to control that all needed tables has appropriate triggers (we need N + 1 triggers with unique code, where N is the number of referring tables). So, built-in support looks much more interesting... On Sun, Sep 21, 2008 at 8:46 AM, Joshua D. Drake <jd@commandprompt.com> wrote: > David Fetter wrote: >> >> On Sun, Sep 21, 2008 at 04:38:56AM +0400, Dmitry Koterov wrote: >>> >>> Hello. >>> >>> Is it possible to create a foreign key constraint for ALL elements of >>> an array field? >> >> Whether it's possible or not--it probably is--it's a very bad idea. >> Just normalize :) > > +1 > >> >> Cheers, >> David. > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Simon Riggs wrote: > No, its not possible. Need a trigger. > > I think we should support it though. If we extend the relational model > with arrays then it would be sensible if we support this aspect as > well. > > Implementation would be fairly straightforward. ri_triggers currently > assumes a non-array value is being checked, but that could be changed to > IN(array). Multi-column keys with arrays sound confusing though. > > What's the syntax going to look like? cheers andrew
On Sun, Sep 21, 2008 at 10:49:56PM +0400, Dmitry Koterov wrote: > Normalization is not a panacea here. Sometimes such normalization > creates too much overeat and a lot of additional code (especially if > there are a lot of such dependencies). Array support in Postgres is > quite handy; in my practive, moving from a_b_map to arrays > economizes hundreds of lines of stored procedure and calling > application code. There are plenty of ways to "economize," as you put it. The burden is on you to demonstrate that you are doing the right thing here because standard database practice hammered out over decades is to normalize. It's possible to make writeable VIEWs that accomplish what you appear to want, but there's no reason to go further than that on the PostgreSQL side. :) > Triggers are not very helpful here, because it is too boringly to > control that all needed tables has appropriate triggers (we need N + > 1 triggers with unique code, where N is the number of referring > tables). > > So, built-in support looks much more interesting... I strongly suspect you'd benefit a lot more by learning database best practices rather than assuming, as you appear to be doing, that you are dealing with a new field and that you know it best. Neither is true. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
> I strongly suspect you'd benefit a lot more by learning database best > practices rather than assuming, as you appear to be doing, that you > are dealing with a new field and that you know it best. Neither is true. Of course, you absolutely right. I venerate you! Ommmm! :-)
On Sun, 2008-09-21 at 15:07 -0400, Andrew Dunstan wrote: > > Simon Riggs wrote: > > No, its not possible. Need a trigger. > > > > I think we should support it though. If we extend the relational model > > with arrays then it would be sensible if we support this aspect as > > well. > > > > Implementation would be fairly straightforward. ri_triggers currently > > assumes a non-array value is being checked, but that could be changed to > > IN(array). Multi-column keys with arrays sound confusing though. > > > > What's the syntax going to look like? The ALTER TABLE would have exactly the same syntax. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
On Sep 21, 2008, at 4:18 AM, Simon Riggs wrote: > On Sun, 2008-09-21 at 04:38 +0400, Dmitry Koterov wrote: > >> Is it possible to create a foreign key constraint for ALL elements of >> an array field? >> >> CREATE TABLE a(id INTEGER); >> CREATE TABLE b(id INTEGER, a_ids INTEGER[]); >> >> Field b.a_ids contains a list of ID's of "a" table. I want to ensure >> that each element in b.a_ids exists in a in any time. Is it possible >> to create an automatic foreign key? > > No, its not possible. Need a trigger. > > I think we should support it though. If we extend the relational model > with arrays then it would be sensible if we support this aspect as > well. +1. And for everyone who immediately jumped to "NORMALIZE!" as the answer, consider that that means a bare minimum of 24 bytes overhead per item that would go into the array. It's not hard at all for that overhead to become massive. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828