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

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';

"Eli Green" <eli@geeky.net> writes:
> The columns listed in constraint_column_usage in the SQL92 information
> schema are from the wrong "side" of the key.

Are you certain this is wrong?  The SQL99 spec is not exactly readable on
the matter, but as best I can tell the behavior we have follows the
spec.  The portion of the spec's CONSTRAINT_COLUMN_USAGE view definition
that's concerned with foreign keys is

  SELECT PK.TABLE_CATALOG, PK.TABLE_SCHEMA, PK.TABLE_NAME, PK.COLUMN_NAME,
         FK.CONSTRAINT_CATALOG, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME
  FROM DEFINITION_SCHEMA.REFERENTIAL_CONSTRAINTS AS FK
       JOIN
       DEFINITION_SCHEMA.KEY_COLUMN_USAGE AS PK
       ON ( FK.UNIQUE_CONSTRAINT_CATALOG, FK.UNIQUE_CONSTRAINT_SCHEMA, FK.UNIQUE_CONSTRAINT_NAME )
        = ( PK.CONSTRAINT_CATALOG, PK.CONSTRAINT_SCHEMA, PK.CONSTRAINT_NAME )

and it sure looks to me like that ought to put out the column names of
the columns associated with the referential constraint's underlying
unique constraint.  Which is what we do.

I tend to agree that the other behavior might be more useful, but we're
going to need more evidence that it's wrong to change it.  Has anyone
tried this example on Oracle or DB2 or SQL Server?

            regards, tom lane

Eli Green wrote:
> This makes it impossible to know column information for both sides of
> a foreign key.

I think the information you want is in KEY_COLUMN_USAGE.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 2007-02-23, Tom Lane wrote:
>"Eli Green" <eli@geeky.net> writes:
>> The columns listed in constraint_column_usage in the SQL92 information
>> schema are from the wrong "side" of the key.
>
>Are you certain this is wrong?  The SQL99 spec is not exactly readable on
>the matter, but as best I can tell the behavior we have follows the
>spec.  The portion of the spec's CONSTRAINT_COLUMN_USAGE view definition
>that's concerned with foreign keys is
>
>  SELECT PK.TABLE_CATALOG, PK.TABLE_SCHEMA, PK.TABLE_NAME, PK.COLUMN_NAME,
>         FK.CONSTRAINT_CATALOG, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME
>  FROM DEFINITION_SCHEMA.REFERENTIAL_CONSTRAINTS AS FK
>       JOIN
>       DEFINITION_SCHEMA.KEY_COLUMN_USAGE AS PK
>       ON ( FK.UNIQUE_CONSTRAINT_CATALOG, FK.UNIQUE_CONSTRAINT_SCHEMA, FK.UNIQUE_CONSTRAINT_NAME )
>        = ( PK.CONSTRAINT_CATALOG, PK.CONSTRAINT_SCHEMA, PK.CONSTRAINT_NAME )
>
>and it sure looks to me like that ought to put out the column names of
>the columns associated with the referential constraint's underlying
>unique constraint.  Which is what we do.
>
>I tend to agree that the other behavior might be more useful, but we're
>going to need more evidence that it's wrong to change it.  Has anyone
>tried this example on Oracle or DB2 or SQL Server?
>
>            regards, tom lane

Sorry for not checking the spec first, I'm doing my testing at home
without access to the internet.

I've tried this on SQL Server 2000; the only other database I have
access to which attempts to implement the entire information_schema. No
Oracle or DB2. MySQL doesn't implement referential_constraints.

It could be that I'm wrong and SQL Server has implemented it
incorrectly.  Initially I thought that this was the only place to get
information about both sides of the foreign key but since they are both
keys, I can get the list of columns (with ordinal_position to join
against) from key_column_usage for the unique key and the non-unique
key (the foreign key itself).

Does that make any sense?

In any case, sorry I said anything and curse Microsoft for implementing
it wrong and making me doubt postgresql.