Thread: Foreign key in composite values

Hello,

I have a composite type with 2 fields.  I would like to check that one of these fields exists in another table.

Foreign keys on a composite field does not seem to be supported.
Is there another way to do that ?

Thanks for your reply
Vincent De Groote

Vincent De Groote <vdg.encelade@gmail.com> writes:
> I have a composite type with 2 fields.  I would like to check that one
> of these fields exists in another table.
> Foreign keys on a composite field does not seem to be supported.

Works for me, in 8.4 and up.  Whether it's a good idea is a different
issue (I think it'll be pretty inefficient, compared to a multicolumn
primary/foreign key on native datatypes), but it works.

regression=# create type cx as (r float8, i float8);
CREATE TYPE
regression=# create table p (id cx primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p_pkey" for table "p"
CREATE TABLE
regression=# create table s (c cx references p);
CREATE TABLE

            regards, tom lane

In your example can you Make cx.r a foreign key on another table (say real) so that a valid instance of cx must have a
valuefor r that exists in real?  I guess you could make r an enum but that wouldn't readily allow you to modify the
allowablevalues for r.  A domain and/or check constraint for r would be another option as well. 

David J.

On May 6, 2011, at 10:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Vincent De Groote <vdg.encelade@gmail.com> writes:
>> I have a composite type with 2 fields.  I would like to check that one
>> of these fields exists in another table.
>> Foreign keys on a composite field does not seem to be supported.
>
> Works for me, in 8.4 and up.  Whether it's a good idea is a different
> issue (I think it'll be pretty inefficient, compared to a multicolumn
> primary/foreign key on native datatypes), but it works.
>
> regression=# create type cx as (r float8, i float8);
> CREATE TYPE
> regression=# create table p (id cx primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p_pkey" for table "p"
> CREATE TABLE
> regression=# create table s (c cx references p);
> CREATE TABLE
>
>            regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general