Problema con las claves foraneas (errno 150)

[Proteus1989]

Os pongo directamente el código a ver si conseguís dar con el error, porque yo no lo encuentro por ningún lado.
El error me da al crear la tabla privates_zones

Código (sql) [Seleccionar] Expandir


-- -----------------------------------------------------
-- 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;


Espero sepáis resolverme la duda.

[RevangelyonX]

Hola,

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

Código [Seleccionar] Expandir

-- -----------------------------------------------------
-- 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 [Seleccionar] Expandir

> SHOW ENGINE INNODB STATUS

Donde verás errores como el siguiente:

Código [Seleccionar] Expandir

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