Thread: Getting FK relationships from information_schema
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. Here is
a test script to be run on a database called "test."
------------------------------------------------------------------------------------
-- Demonstrate issues with information_schema
create table empl ( empl_pk int4 primary key, name varchar
);
create table empl_addr ( empl_k int4 references empl, type varchar, address varchar, primary
key(empl_k, type)
);
create table doc ( doc_pk int4 primary key, name varchar
);
create table empl_doc ( empl_k int4, doc_k int4,
primary key (empl_k,doc_k), foreign key (empl_k) references empl, foreign key (doc_k) references doc
);
create table doc_empl ( doc_k int4, empl_k int4,
primary key (doc_k,empl_k), foreign key (doc_k) references doc, foreign key (empl_k) references empl
);
-- Show info about the tables' primary keys
select tc.table_name, tc.constraint_type, cu.column_name, cu.ordinal_position
from information_schema.key_column_usage cu, information_schema.table_constraints tc
where cu.constraint_name = tc.constraint_name and cu.table_name = tc.table_name
and tc.constraint_type = 'PRIMARY KEY'
and tc.table_catalog = 'test' and tc.table_schema = 'public' order by 2
;
-- Show info about the tables' foreign keys
select tc.table_name, tc.constraint_type, cu.column_name, cu.ordinal_position
from information_schema.key_column_usage cu, information_schema.table_constraints tc
where cu.constraint_name = tc.constraint_name and cu.table_name = tc.table_name
and tc.constraint_type = 'FOREIGN KEY'
and tc.table_catalog = 'test' and tc.table_schema = 'public' order by 2
;
-- Show constraints for our table
select constraint_name, table_name, constraint_type, is_deferrable, initially_deferred
from information_schema.table_constraints
order by 2,3,1
;
-- Show foreign key constraints
select
-- * constraint_name, unique_constraint_name, match_option,update_rule,delete_rule
from information_schema.referential_constraints
order by 2,1
;
------------------------------------------------------------------------------------
This produces the following output:
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLEtable_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------empl | PRIMARY KEY | empl_pk |
1empl_addr | PRIMARY KEY | empl_k | 1empl_addr | PRIMARY KEY | type |
2doc | PRIMARY KEY | doc_pk | 1empl_doc | PRIMARY KEY | empl_k |
1empl_doc | PRIMARY KEY | doc_k | 2doc_empl | PRIMARY KEY | doc_k |
1doc_empl | PRIMARY KEY | empl_k | 2
(8 rows)
table_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------empl_addr | FOREIGN KEY | empl_k |
1empl_doc | FOREIGN KEY | empl_k | 1empl_doc | FOREIGN KEY | doc_k |
1doc_empl | FOREIGN KEY | doc_k | 1doc_empl | FOREIGN KEY | empl_k |
1
(5 rows)
constraint_name | table_name | constraint_type | is_deferrable |
initially_deferred
-----------------+------------+-----------------+---------------+--------------------doc_pkey | doc |
PRIMARYKEY | NO | NO$1 | doc_empl | FOREIGN KEY | NO | NO$2 |
doc_empl | FOREIGN KEY | NO | NOdoc_empl_pkey | doc_empl | PRIMARY KEY | NO |
NOempl_pkey | empl | PRIMARY KEY | NO | NO$1 | empl_addr | FOREIGN KEY | NO
| NOempl_addr_pkey | empl_addr | PRIMARY KEY | NO | NO$1 | empl_doc | FOREIGN
KEY | NO | NO$2 | empl_doc | FOREIGN KEY | NO | NOempl_doc_pkey |
empl_doc | PRIMARY KEY | NO | NO
(10 rows)
constraint_name | unique_constraint_name | match_option | update_rule |
delete_rule
-----------------+------------------------+--------------+-------------+-------------$1 | doc_pkey
| NONE | NO ACTION |
NO ACTION$2 | doc_pkey | NONE | NO ACTION |
NO ACTION$1 | empl_pkey | NONE | NO ACTION |
NO ACTION$1 | empl_pkey | NONE | NO ACTION |
NO ACTION$2 | empl_pkey | NONE | NO ACTION |
NO ACTION
(5 rows)
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.
For example, if I want to know about the foreign keys for empl_doc, I
can determine from query 3 that there are two foreign key constraints on
that table and they are called $1 and $2. From query 4, I should be
able to see what primary key constraints they point to, except there are
3 $1's and 2 $2's in that table so I can't really see where they point.
(Notice that the third and fourth records in the last query are identical!)
Should I be looking somehow else in information_schema to see where
foreign keys link? Or am I correct that either:
1. The automatically generated FK constraint_name should be guaranteed
to be unique; or
2. There should be an additional column in the last query somewhere to
tell me what table the FK reference is coming from.
Kyle
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
Tom Lane wrote: <blockquote cite="mid26677.1086673982@sss.pgh.pa.us" type="cite"><pre wrap="">Kyle <a class="moz-txt-link-rfc2396E"href="mailto:kyle@actarg.com"><kyle@actarg.com></a> writes: </pre><blockquote type="cite"><prewrap="">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. </pre></blockquote><pre wrap=""> 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. </pre></blockquote> I think this is only an issue when the user relies on postgres to choose a constraintname automatically. Seems like a reasonable approach would be to have postgres choose a name for the constraintthat happens to be unique in the schema (like tablename_fkey_$1). Then if the user wants to make named constraintsthat all have the same name, he can (and information_schema will be less useful) or he can rely on the automaticallygenerated names to be a bit more descriptive in information_schema.<br /><blockquote cite="mid26677.1086673982@sss.pgh.pa.us"type="cite"><pre wrap="">Can we get away with adding implementation-specific columns to information_schema tables? If not, what other alternatives are there? </pre></blockquote> Another possible approach: Does the constraint name showing up in information_schema really have tobe the _real_ name? Or can we just make a name consisting of the table name appended to the internal postgres constraintname.<br /><br /> I think I like this less than the first idea.<br /><br /> Kyle<br /><br />
Kyle <kyle@actarg.com> writes:
> I think this is only an issue when the user relies on postgres to choose
> a constraint name automatically. Seems like a reasonable approach would
> be to have postgres choose a name for the constraint that happens to be
> unique in the schema (like tablename_fkey_$1).
We have discussed changing the default names of FK constraints before.
I have no problem with doing something like the above --- any objection
out there?
(Of course, this is only a long-term fix for your original problem,
as it'll take a good long while for any such naming change to propagate
to Joe's-Corner-Bar's database.)
regards, tom lane
Tom Lane wrote: > Kyle <kyle@actarg.com> writes: > > I think this is only an issue when the user relies on postgres to > > choose a constraint name automatically. Seems like a reasonable > > approach would be to have postgres choose a name for the constraint > > that happens to be unique in the schema (like tablename_fkey_$1). > > We have discussed changing the default names of FK constraints > before. I have no problem with doing something like the above --- any > objection out there? I think it's a good idea. It will also make the error messages of the kind "foreign key $1 violated" a bit clearer by default. There will, however, be complaints that the constraint names are not automatically renamed with the table; but we are used to those by now.
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> We have discussed changing the default names of FK constraints
>> before. I have no problem with doing something like the above --- any
>> objection out there?
> I think it's a good idea. It will also make the error messages of the
> kind "foreign key $1 violated" a bit clearer by default.
Right ho --- I'm working on a patch that will choose default names
of the form table_column_checkN and table_column_fkeyN, similar to
what we already do for index constraints.
regards, tom lane