Re: [BUGS] Meaning of pg_constraint.conindid for foreign keys

Roy Brokvam <roy.brokvam@gmail.com> writes:
> To my surprise, the query did not return any rows, even though I knew there
> existed indexless foreign keys referencing my table. After investigating
> further, saw that conindid contained the oid of the referenced table's
> primary key, not the oid of the index "implementing" the foreign key.

Not what I see here:

regression=# create table foo1(f1 int primary key);
CREATE TABLE
regression=# create table foo2(f1 int);
CREATE TABLE
regression=# create unique index on foo2(f1);
CREATE INDEX
regression=# create table bar1 (f1 int references foo1(f1));
CREATE TABLE
regression=# create table bar2 (f1 int references foo2(f1));
CREATE TABLE
regression=# select oid,conname,contype,conrelid::regclass,conindid,conindid::regclass,confrelid::regclass from
pg_constraintorder by oid desc limit 3; oid   |   conname    | contype | conrelid | conindid |  conindid   | confrelid  
--------+--------------+---------+----------+----------+-------------+-----------232357 | bar2_f1_fkey | f       | bar2
   |   232353 | foo2_f1_idx | foo2232345 | bar1_f1_fkey | f       | bar1     |   232337 | foo1_pkey   | foo1232338 |
foo1_pkey   | p       | foo1     |   232337 | foo1_pkey   | - 
(3 rows)

foo1's pkey constraint has OID 232338, its underlying index has
OID 232337, and the latter is what's in conindid for bar1's
foreign key constraint.  It has to be that way because of the
case illustrated by foo2/bar2, where an FK depends on an index
that doesn't have a formal constraint associated with it.
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs