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

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.