Submitted: 25 May 2005 11:14 Modified: 16 Jul 2006 17:27
Reporter: Anders Karlsson Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.4 OS:
Assigned to: Sergei Glukhov CPU Architecture:Any

[25 May 2005 11:14] Anders Karlsson
The table REFERENTIAL_CONSTRAINTS, which lists relations between referencing constraints, i.e. FK -> PK and FK -> UNIQUE constraints, is missing from the INFORMATION_SCHEMA. This is a very useful and important tables, as it allows you to figure out dependencies between tables when using FK relationships.

How to repeat:
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
| Tables_in_information_schema          |
| SCHEMATA                              |
| TABLES                                |
| COLUMNS                               |
| CHARACTER_SETS                        |
| COLLATIONS                            |
| ROUTINES                              |
| STATISTICS                            |
| VIEWS                                 |
| USER_PRIVILEGES                       |
| SCHEMA_PRIVILEGES                     |
| TABLE_PRIVILEGES                      |
| COLUMN_PRIVILEGES                     |
| TABLE_CONSTRAINTS                     |
| KEY_COLUMN_USAGE                      |
15 rows in set (0.00 sec)

[14 Mar 2006 22:48] Beat Vontobel
The addition of REFERENTIAL_CONSTRAINTS to the INFORMATION_SCHEMA is really most wanted and I'm a little bit disappointed it's now in status Open for 293 days already. There is currently no way to get all the information on FOREIGN KEYs from MySQL's INFORMATION_SCHEMA. Of course there is KEY_COLUMN_USAGE, but that doesn't contain any information on ON UPDATE and ON DELETE clauses for example.
[14 Mar 2006 22:56] MySQL Verification Team
Thank you for the bug report and feature request.
[14 Mar 2006 23:06] Beat Vontobel
Thanks Miguel, that was a very quick reaction ;-)

I'm currently working on an ugly workaround to parse the ON UPDATE and ON DELETE information out of SHOW CREATE TABLE and will put it in a stored function. But I think the addition of REFERENTIAL_CONSTRAINTS in the MySQL source code shouldn't be too much work - and it could probably safe others from doing similar things as I do right now.
[7 May 2006 0:51] Beat Vontobel
Note that this has just been added to 5.1.10. Someone should probably close this bug. It would however be _very_ helpful if this would be also backported to the 5.0 tree (which wouldn't probably be too difficult and shouldn't break other things, as it will be a quite isolated feature), given that it might take some more time until a production release of 5.1 will be available. Are there any plans for this?
[16 Jul 2006 10:04] Beat Vontobel
As I said, this feature request could be closed as it has been added to 5.1. Any plans to backport to 5.0 (which will be the production release for probably some more months). Any feedback from anybody at MySQL?
[16 Jul 2006 17:27] Jim Winstead
The REFERENTIAL_CONSTRAINTS table was added in MySQL 5.1.10.

It won't be backported to 5.0.