From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kyle <kyle@actarg.com>
Cc: pgsql-sql@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Subject: Re: Getting FK relationships from information_schema
Date: Tue, 08 Jun 2004 01:53:02 -0400
Message-ID: <26677.1086673982@sss.pgh.pa.us> (raw)
In-Reply-To: <40C500D8.9060009@actarg.com>
References: <40C500D8.9060009@actarg.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kyle@actarg.com, peter_e@gmx.net
Subject: Re: Getting FK relationships from information_schema
In-Reply-To: <26677.1086673982@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox