Thread: Foreign key constraint for array-field?

Foreign key constraint for array-field?

From
"Dmitry Koterov"
Date:
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?


Re: Foreign key constraint for array-field?

From
"Merlin Moncure"
Date:
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


Re: Foreign key constraint for array-field?

From
David Fetter
Date:
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


Re: Foreign key constraint for array-field?

From
"Joshua D. Drake"
Date:
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.



Re: Foreign key constraint for array-field?

From
Simon Riggs
Date:
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



Re: Foreign key constraint for array-field?

From
"Dmitry Koterov"
Date:
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
>


Re: Foreign key constraint for array-field?

From
Andrew Dunstan
Date:

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


Re: Foreign key constraint for array-field?

From
David Fetter
Date:
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


Re: Foreign key constraint for array-field?

From
"Dmitry Koterov"
Date:
> 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! :-)


Re: Foreign key constraint for array-field?

From
Simon Riggs
Date:
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



Re: Foreign key constraint for array-field?

From
Decibel!
Date:
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