Re: Array values and foreign keys

From: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
To: "Daniel Savard" <dsavard(at)cids(dot)ca>, pgsql-general(at)postgresql(dot)org
Subject: Re: Array values and foreign keys
Date: 2004-10-27 15:59:46
Message-ID: opsgjddwmocq72hf@musicbox
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

You can't express it directly with a CHECK constraint but you can do this

- add CHECK( test_array( yourcolumn )) in your table definition
- create function test_array which takes an array and looks if all its
elements are in your table T2, I do something like comparing the length of
the array to SELECT count(1) FROM T2 WHERE key IN array
You can do it other ways but you'll have to use a function.

On Wed, 27 Oct 2004 10:19:02 -0400, Daniel Savard <dsavard(at)cids(dot)ca> wrote:

> Is there a way to define a foreign key for the values of an array?
> For example, if table T1 is having a colum A which is defined as
> integer[] can I define a foreign key in order to force each value to be
> a pointer (index) to a row in a table T2?
> If yes, how? Is there any shortcomings to this approach?
> Thanks,
> Daniel Savard
> ---
> Daniel Savard
> dsavard(at)cids(dot)ca
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

In response to


Browse pgsql-general by date

  From Date Subject
Next Message f-f 2004-10-27 16:07:54 Re:
Previous Message Thomas F.O'Connell 2004-10-27 15:56:39 Re: Newbie question about escaping in a function