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

"Eli Green" <eli(at)geeky(dot)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