Re: Defining a foreign key with a duplicate column is broken

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> I wasn't quite sure if it was possible to include the same column twice in
> a foreign key, so I tested....

> create table r1 (a int);
> create table r2 (b int);
> create unique index on r2(b,b);
> alter table r1 add constraint r2_b_fkey foreign key (a,a) references r2
> (b,b);
> ERROR: cache lookup failed for opclass 0

Ouch.

> The attached seems to fix the problem, but the whole thing makes me wonder
> if this is even meant to be allowed? I was thinking that this might be a
> good time to disallow this altogether, since it's already broken and looks
> like it has been for about 11 years

We've gone out of our way in the past to allow duplicate index columns
(eg commit cfc5008a51f4), so I'm not sure why we'd not allow such indexes
to be used as foreign key references. The example you posted above does
look pretty pointless, but something like this is perhaps less so:

regression=# create table r1 (a int, c int);
CREATE TABLE
regression=# create table r2 (b int);
CREATE TABLE
regression=# create unique index on r2(b,b);
CREATE INDEX
regression=# alter table r1 add constraint r2_b_fkey foreign key (a,c) references r2
(b,b);
ERROR: cache lookup failed for opclass 0

especially when using nondefault FK match rules.

regards, tom lane