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

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 (view raw or flat)
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

pgsql-bugs by date

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

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