The following bug has been logged online:
Bug reference: 3038
Logged by: Eli Green
Email address: eli@geeky.net
PostgreSQL version: 7.4.5 - 8.?
Operating system: Linux and Windows XP
Description: information_schema.constraint_column_usage has wrong
information for foreign keys
Details:
The columns listed in constraint_column_usage in the SQL92 information
schema are from the wrong "side" of the key. It is my understanding that
referential_constraints should contain the unique key that the foreign key
refers to and constraint_column_usage should naturally contain the "other
side" of the foreign key; the portion that is not necessarily unique. This
is not how postgresql does it. I have tested this on Linux/Fedora Core
(PostgreSQL 7.4.5) and on Windows XP (PostgreSQL 8.2, I think).
In the example below, the results of the first query should be:
dog_to_owner, person_pkey
The results of the second query should be:
dog, owner_id
Instead, the results of the second query are:
person, id
This makes it impossible to know column information for both sides of a
foreign key.
SQL Server 2000 (and presumably later versions) returns the information as
expected.
--- TEST CASE
drop table dog;
drop table person;
create table person (id int not null, name text);
alter table person add constraint person_pkey primary key (id);
create table dog (owner_id int, name text);
alter table dog add constraint dog_to_owner foreign key (owner_id)
references person (id);
select constraint_name, unique_constraint_name from
information_schema.referential_constraints;
select table_name, column_name from
information_schema.constraint_column_usage where
constraint_name='dog_to_owner';
