On 2007-02-23, Tom Lane wrote:
"Eli Green" <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
