Hi, Everyone
i am a mysql newbie, i come here for a innodb puzzle, i have a sql file like following

create table Nations ( NationID tinyint unsigned auto_increment primary key,
Country varchar(40)
) engine = innodb;


create table Positions ( PosID tinyint unsigned auto_increment primary key,
Position varchar(10)
) engine = innodb;


create table Clubs ( ClubID tinyint unsigned auto_increment primary key,
Name varchar(40) not null unique,
Nationality tinyint unsigned,
foreign key FK_Nation (Nationality) REFERENCES Nations (NationID)
) engine = innodb;

create table Players (PlayerID int auto_increment primary key,
Name varchar(30) not null,
Nationality tinyint unsigned,
Height float,
weight tinyint unsigned,
Position tinyint unsigned,
Club tinyint unsigned,
Salary tinyint unsigned,
key ( Nationality ),
CONSTRAINT FK_Nation foreign key ( Nationality ) REFERENCES Nations (NationID),
foreign key FK_Postion ( Position ) REFERENCES Positions (PosID),
foreign key FK_Club ( Club ) REFERENCES Clubs (ClubID)
) engine = innodb;

if i put definition of table Players before Clubs/Nations/Positions, then the innodb monitor shows me that it cannot resolve table Nations (NationID), if i change the storage enigne to myisam, it will be ok whether i put the definition before referenced table or not.

that is correct -- the referenced table should be declared before the referencing table references it

in myisam it doesn't matter, because myisam doesn't support foreign keys