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

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

From: Eli Green <eli(at)geeky(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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:57:18
Message-ID: 20070223165718.GB3439@twobox.geeky.net (view raw or flat)
Thread:
Lists: pgsql-bugs
On 2007-02-23, Tom Lane wrote:
>"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

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.


In response to

pgsql-bugs by date

Next:From: Keith TurnerDate: 2007-02-23 17:00:18
Subject: Spelling error in 8.1.6-1 Windows Error Dialog typo
Previous:From: Craig WhiteDate: 2007-02-23 16:39:20
Subject: Re: BUG #3032: Commit hung for days

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