Re: Meaning of pg_constraint.conindid for foreign keys

>>>>> "Roy" == Roy Brokvam <roy(dot)brokvam(at)gmail(dot)com> writes:

Roy> After investigating further, saw that conindid contained the oid
Roy> of the referenced table's primary key, not the oid of the index
Roy> "implementing" the foreign key.

FKs aren't implemented using an index on the referencing table, only on
the referenced table.

i.e. given:

CREATE TABLE foo (id integer PRIMARY KEY);
CREATE TABLE bar (foo_id integer REFERENCES foo);

the constraint depends on the existence of the index foo_pkey (and it's
this one which is recorded in the system catalogs), but there is no
index at all on bar; even if you created an index on bar(foo_id), it
would not be associated in any way with the FK constraint (though it
would improve the performance of updates/deletes in foo, because the
query issued by the constraint check would likely use it).

So you're looking in the wrong place.

--
Andrew (irc:RhodiumToad)