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

>>>>> "Roy" == Roy Brokvam <roy.brokvam@gmail.com> writes:
Roy> After investigating further, saw that conindid contained the oidRoy> of the referenced table's primary key, not
theoid of the indexRoy> "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)


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