MySQL Bugs: #10857: INFORMATION_SCHEMA missing REFERENTIAL

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

Description:
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                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| ROUTINES                              |
| STATISTICS                            |
| VIEWS                                 |
| USER_PRIVILEGES                       |
| SCHEMA_PRIVILEGES                     |
| TABLE_PRIVILEGES                      |
| COLUMN_PRIVILEGES                     |
| TABLE_CONSTRAINTS                     |
| KEY_COLUMN_USAGE                      |
+---------------------------------------+
15 rows in set (0.00 sec)

mysql>

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.

Thank you for the bug report and feature request.

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.

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?

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?

The REFERENTIAL_CONSTRAINTS table was added in MySQL 5.1.10.

It won't be backported to 5.0.