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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Eli Green" <eli(at)geeky(dot)net>
Cc: pgsql-bugs(at)postgresql(dot)org, Peter Eisentraut <peter_e(at)gmx(dot)net>
Subject: Re: BUG #3038: information_schema.constraint_column_usage has wrong information for foreign keys
Date: 2007-02-23 16:35:20
Message-ID: 528.1172248520@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs

"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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Craig White 2007-02-23 16:39:20 Re: BUG #3032: Commit hung for days
Previous Message Tom Lane 2007-02-23 16:16:58 Re: BUG #3048: pg_dump dumps intarray metadata incorrectly