MySQL Bugs: #3283: Extracting Foreign Key Information

Bug #3283	Extracting Foreign Key Information
Submitted:	24 Mar 2004 8:49	Modified:	2 Dec 2005 16:45
Reporter:	Tim Gustafson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.18	OS:	FreeBSD (FreeBSD 4.9-STABLE)
Assigned to:		CPU Architecture:	Any

Description:
It would be REALLY handy to have a command that shows you all the foreign keys in a certain table in a format similar to this.  For example, something like this:

SHOW FOREIGN KEYS FROM Items;

It would also be equally handy to be able to report on which other tables have foreign keys that link TO a certain table.

SHOW FOREIGN KEYS TO Items;

Both these commands should (in my opinion) output something like this:

SHOW FOREIGN KEYS FROM Items;

+-------------+---------------+-------------+--------------+----------+----------+
| MasterTable | MasterColumns | SlaveTable  | SlaveColumns | OnDelete | OnUpdate |
+-------------+---------------+-------------+--------------+----------+----------+
| Items       | ID            | ItemOptions | Item         | Restrict | Restrict |
| Items       | ID            | ItemSizes   | Item         | Restrict | Restrict |
+-------------+---------------+-------------+--------------+----------+----------+

This information would be very helpful when, for example, you are making a PHP page that allows you to update tables and you want to be able to dynamically link to parent and child tables.

I hope this all makes sense.  :)

Thanks in advance, and keep up the GREAT work!

How to repeat:
This is not a bug.

Thank you for a feature request. One way to get this information is already implemented in MySQL 5, in the INFORMATION_SCHEMA. Read
http://dev.mysql.com/doc/refman/5.0/en/table-constraints-table.html and http://dev.mysql.com/doc/refman/5.0/en/key-column-usage-table.html for the detais.