mysql - Using one of the columns in a composite key as a foreign key -

i trying see whether can use 1 of columns in composite key foreign key. , got strange result.

create table testparent(     pk1 int,     pk2 int,     primary key(pk1,pk2) );  query ok, 0 rows affected (0.01 sec)   create table testchild1(     fk1 int,      foreign key (fk1) references testparent(pk1) );  query ok, 0 rows affected (0.01 sec)   create table testchild2(     fk2 int,     foreign key (fk2) references testparent(pk2) );  error 1005 (hy000): can't create table 'test.testchild2' (errno: 150) 

mysql allows create foreign key reference first column in primary key, not second column. strange? or being stupid!

as mysql documentation on foreign keys indicates:

innodb permits foreign key reference index column or group of columns. however, in referenced table, there must index referenced columns listed first columns in same order.

ndb requires explicit unique key (or primary key) on column referenced foreign key.

so, if use innodb, mysql not allow create foreign key on field not leftmost field in index.

the reason in multi-column index cannot value based on field not left most, therefore index cannot used value foreign key check.

this behaviour of mysql indexes described in mysql documentation on multi-column indexes:

mysql cannot use index perform lookups if columns not form leftmost prefix of index.