Skip site navigation (1) Skip section navigation (2)

BUG #3038: information_schema.constraint_column_usage has wrong information for foreign keys

From: "Eli Green" <eli(at)geeky(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3038: information_schema.constraint_column_usage has wrong information for foreign keys
Date: 2007-02-20 16:31:58
Message-ID: 200702201631.l1KGVwZS054571@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3038
Logged by:          Eli Green
Email address:      eli(at)geeky(dot)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';

Responses

pgsql-bugs by date

Next:From: JessicaDate: 2007-02-20 18:05:30
Subject: BUG #3039: install completely but can't connect to server
Previous:From: Pavel StehuleDate: 2007-02-20 16:31:30
Subject: BUG #3037: strange behave of CHECK constraint

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group