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:

[2 Apr 2005 22: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 4: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 8:37] Sergey Gluhov 
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 22:08] Paul DuBois 
Noted in 5.0.6 changelog and description for
the KEY_COLUMN_USAGE table.