Foreign key constraint for array-field?

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

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.com
PostgreSQL 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...

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

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.com
PostgreSQL Training, Services and Support

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 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