Re: Getting FK relationships from information_schema

Kyle <kyle@actarg.com> writes:
> I'm trying to get my application to deduce foreign key relationships 
> automatically so it can perform appropriate joins for the user.  I'm new 
> to information_schema and having problems getting what I want.
> ...
> I can determine all the primary key fields nicely, and I can tell what 
> fields are foreign keys.  The problem is, I can't determine where the 
> foreign keys are pointing.  The problem is, the constraint names ($1, 
> $2, etc.) are not unique so I don't know how to join the third query 
> into the fourth.

Hmm, this is messy :-(.  The SQL spec requires constraint names to be
unique within a schema.  Postgres doesn't require them to be unique even
within a table.  We were aware that there were some compatibility issues
there, but I hadn't realized that the information_schema design is
fundamentally dependent on the assumption of schema-wide uniqueness for
these names.

For a number of reasons (backwards compatibility being the hardest to
argue with), adopting the spec's restriction on constraint names seems
unlikely to happen.  You could of course follow it within your own
database designs, but I don't foresee Postgres enforcing it on
everyone.

In the short run I think your only answer is to dig deeper than
information_schema and look directly at the Postgres catalogs.
In the long run it'd be nice to have a cleaner answer, but I'm not
sure what it ought to look like.  Can we get away with adding
implementation-specific columns to information_schema tables?
If not, what other alternatives are there?
        regards, tom lane