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 22:04	Modified:	24 May 2005 22:08
Reporter:	Niels G
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Any (All)
Assigned to:	Sergey Gluhov	Target Version:

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.