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