Bug #15724 FK to non-unique parent leads to information_schema.key_column_usage anomaly
Submitted: 13 Dec 2005 23:34 Modified: 9 Jan 2006 18:36
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.16 OS:NA
Assigned to: Paul DuBois CPU Architecture:Any

[13 Dec 2005 23:34] Roland Bouman
Description:
Foreign keys can be defined provided that both tables are innodb tables and both tables have an index for which the leading columns have identical datatypes.

An important deviation from other rdbms-es is the fact that the referenced (parent) index is not required to be a unique index. That is, it is possible to create a foreign key that references a table that has neither a primary key nor a unique constraint.

Apart from the fact that this could be documented more clearly (fragements of the documentation found at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html only hint to this, and never mention explicitly that this type of foreign key is non-standard. However, there is explicit mention of foreign key checking in this case marked as non-standard), these "many to many" foreign keys lead to a strange anomaly in the information_schema.key_column_usage system view.

In this system view, the columns are reported that take part in table constraints. If the column is in a foreign key, the standard column POSITION_IN_UNIQUE_CONSTRAINT contains the ordinal position of column in the referenced (parent) table constraint. However, in this case the constraint is not a unique constraint. 

How to repeat:
use test;

create table A( 
id int unsigned not null auto_increment 
, name varchar(30) not null 
, constraint pk_A primary key( 
id 
) 
, index i_A ( 
name 
) 
) 
engine=innodb 
; 

create table B( 
id int unsigned not null auto_increment 
, a_name varchar(30) not null 
, constraint pk_b primary key( 
id 
) 
, constraint fk_b_a foreign key ( 
a_name 
) references a ( 
name 
) 
) 
engine=innodb 
; 

select constraint_name
,        column_name
,        position_in_unique_constraint
from   information_schema.key_column_usage
where constraint_schema = 'test'
and    table_name = 'B'

+-----------------+-------------+-------------------------------+
| constraint_name | column_name | position_in_unique_constraint |
+-----------------+-------------+-------------------------------+
| PRIMARY         | id          |                          NULL |
| fk_b_a          | a_name      |                             1 |
+-----------------+-------------+-------------------------------+

Suggested fix:
Please document this type of relationship more clearly. Include a discussiion on the consequences for the information schema. If possible, provide an example as to why this type of relationship is usefull
[14 Dec 2005 12:59] Heikki Tuuri
Dear docs team,

please document that a FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL. It is an InnoDB extension to standard SQL.

Regards,

Heikki
[14 Dec 2005 13:06] Valeriy Kravchuk
Looks like a (verified) documentation request for me.
[9 Jan 2006 18:36] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The note will appear here:

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html