MySQL Bugs: #51174: KEY_COLUMN_USAGE does not report foreign keys

Bug #51174	KEY_COLUMN_USAGE does not report foreign keys
Submitted:	14 Feb 2010 21:19	Modified:	22 Feb 2010 21:11
Reporter:	Kristopher Wilson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.0.84	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When creating tables with foreign keyed columns using the InnoDb engine, the keys do not appear in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.

How to repeat:
mysql> create database testfk;
Query OK, 1 row affected (0.00 sec)

mysql> use testfk;
Database changed

mysql> create table customers(customer_id int primary key) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> create table orders(order_id int primary key, customer_id int references customers(customer_id) on delete cascade) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA = 'testfk' AND REFERENCED_TABLE_NAME IS NOT NULL;
Empty set (0.02 sec)

Suggested fix:
Records should appear in INFORMATION_SCHEMA.KEY_COLUMN_USAGE for the foreign keys.

REFERENCES clause at column level does NOT create foreign key in MySQL. Read http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html. Please, check with foreign key defined at table level.

This is just nonsensical in my opinion.  The documentation linked to just sounds like it is spinning a bug into a feature: "serves only as a memo or comment to you"

The column is, after all, named REFERENCED_TABLE_NAME and the reference was clearly made when creating the table with "customer_id int references customers(customer_id)" - Maybe it shouldn't be called REFERENCED_TABLE_NAME but rather FOREIGN_KEY_TABLE_NAME?  

Column names aside, it seems like a reference *should* trigger a foreign key.  I don't really see any benefit to it not creating a foreign key relationship, especially considering the drawbacks that come with the "feature" of it not doing so.

What exactly does happen when you use "references table(column)" on a column? Is this information stored anywhere? 

It certainly doesn't appear in "show create table orders;" And before you bring it up, yes, I know the documentation explains that it does not show up in "show create table." But that just doesn't make sense. 

So it's basically a comment or memo that disappears making it neither a comment nor a memo, because it can't ever be referenced or seen.

It's essentially akin to an actual SQL comment. Which makes no sense to take the time to make the engine understand this syntax if it's just going to disappear. 

I agree with Kieran; this is a bug being spun into a feature. Probably the most worthless feature I've ever seen.

This is a known and documented limitation, not a (good) feature. 

This limitation is reported as bugs/feature requests elsewhere (bug #25031, bug #20563 etc), and it will NOT disappear today or tomorrow. Thus, it is NOT a bug in information_schema table or SHOW CREATE TABLE statement (unless you can prove that visible and effective foreign key is NOT reflected by that I_S table).

Effectively, what you're telling me is that these two create table statements are identical in function?

CREATE TABLE test(
    test_id int PRIMARY KEY, 
    other_id int REFERENCES other(other_id)
) ENGINE=INNODB;

CREATE TABLE test(
    test_id int PRIMARY KEY, 
    other_id int ' this should be a value in others(other_id)
) ENGINE=INNODB;

Effectively meaning that the column REFERENCES syntax is nothing more than a comment that disappears as soon as the SQL is ran? 

And you mean to tell me that the MySQL developers implemented this precise functionality on purpose? If so, why did a simple SQL comment not suffice? 

This seems like a bug being spun into a feature (and now, according to your last post, limitation). 

And why not fix it? It can't be that difficult of a task, and there shouldn't be any issues with backward compatibility because I sincerely doubt anyone is using this syntax. Because it doesn't do anything...