MySQL Bugs: #9587: information_schema.table_constraints doesn't contain foreign key relationship

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

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.

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.

Fixed in 5.0.6
 "REFERENCED_TABLE_SCHEMA", "REFERENCED_TABLE_NAME", 
 "REFERENCED_COLUMN_NAME" fields are added into
  KEY_COLUMN_USAGE table

Noted in 5.0.6 changelog and description for
the KEY_COLUMN_USAGE table.