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

[24 Mar 2004 8:49] Tim Gustafson
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.
[2 Dec 2005 16:45] Valeriy Kravchuk
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.