Problema con las claves foraneas (errno 150)

-- -----------------------------------------------------
-- Table `mydb`.`guest_zones`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`guest_zones` (
`player_name` VARCHAR(45) NOT NULL ,
`zone_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`player_name`, `zone_name`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`privates_zones`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`privates_zones` (
`zone_name` VARCHAR(45) NOT NULL ,
`player_name` VARCHAR(45) NOT NULL ,
`price` INT NULL ,
PRIMARY KEY (`zone_name`) ,
CONSTRAINT `fk_privates_zones_guest_zones1`
FOREIGN KEY (`zone_name` )
REFERENCES `mydb`.`guest_zones` (`zone_name` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
`player_name` VARCHAR(45) NOT NULL ,
`vip` TINYINT(1) NULL ,
PRIMARY KEY (`player_name`) ,
CONSTRAINT `fk_users_privates_zones1`
FOREIGN KEY (`player_name` )
REFERENCES `mydb`.`privates_zones` (`player_name` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;

Hola,

Al hacer referencia en la segunda tabla al primary key de la primera ya no da error:

Código:

-- -----------------------------------------------------
-- Table `mydb`.`privates_zones`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`privates_zones` (
 `zone_name` VARCHAR(45) NOT NULL ,
 `player_name` VARCHAR(45) NOT NULL ,
 `price` INT NULL ,
 PRIMARY KEY (`zone_name`) ,
 CONSTRAINT `fk_privates_zones_guest_zones1`
   FOREIGN KEY (`player_name`, `zone_name` )
  REFERENCES `mydb`.`guest_zones` (`player_name`,`zone_name` )
   ON DELETE CASCADE
   ON UPDATE CASCADE)
ENGINE = InnoDB;

De todos modos, ejecuta la siguiente sentencia desde la consola de MySQL (suponiendo que tengas privilegios):

Código:

> SHOW ENGINE INNODB STATUS

Donde verás errores como el siguiente:

Código:

LATEST FOREIGN KEY ERROR
...
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Luego en el siguiente enlace tienes posibles causas y solucions del error:

www.eliacom.com/mysql-gui-wp-errno-150.php

Salu2