Re: Foreign key in composite values

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 value for r that exists in real? I guess you could make r an enum but that wouldn't readily allow you to modify the allowable values 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vincent De Groote <vdg(dot)encelade(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general