Foreign key in composite values

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 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@sss.pgh.pa.us> wrote: