Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: Should referenced table of FK be defined before referencing table

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Chengshun Xia View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •