Bug #9587 information_schema.table_constraints doesn't contain foreign key relationship
Submitted: 2 Apr 2005 20:04 Modified: 24 May 2005 20:08
Reporter: Niels G Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Any (All)
Assigned to: Sergei Glukhov CPU Architecture:Any

[2 Apr 2005 20:04] Niels G
Description:
The information_schema.table_constraints and key_column_usage tables do not contain full information about foreign keys.  It contains the list of foreign keys and the columns associated in the primary table, but it does not contain the referenced table or the matching key columns referenced.

How to repeat:
There is no way to get this information as I can tell from the tables in
information_schema, or from the current documentation.

Suggested fix:
This could be fixed in different ways ... one way is to add 4 columns to the TABLE_CONSTRAINTS table ...

REF_CONSTRAINT
REF_TABLE_CATALOG
REF_TABLE_SCHAMA
REF_TABLE_NAME

These columns would be NULL unless the row is a foreign key, which would then have the referenced table name and constraint.
[5 Apr 2005 2:51] Miguel Solorzano
According with the Manual:

http://dev.mysql.com/doc/mysql/en/other-information-schema-tables.html

21.1.16. Other INFORMATION_SCHEMA Tables

We will add more INFORMATION_SCHEMA tables soon. Particularly, we acknowledge
the need for INFORMATION_SCHEMA.PARAMETERS and for
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and for INFORMATION_SCHEMA.TRIGGERS.
[23 May 2005 6:37] Sergei Glukhov
Fixed in 5.0.6
 "REFERENCED_TABLE_SCHEMA", "REFERENCED_TABLE_NAME", 
 "REFERENCED_COLUMN_NAME" fields are added into
  KEY_COLUMN_USAGE table
[24 May 2005 20:08] Paul Dubois
Noted in 5.0.6 changelog and description for
the KEY_COLUMN_USAGE table.