MySQL ERROR: Error 1215: Cannot add foreign key constraint

publicado por: Anonymous

He modelado varios esquemas con el Modeler del “MySQL Workbench” y hasta aquí todo genial.

El problema es que hay dos ForeingKey que no hay forma de que se inserten y me salta con el error:

MySQL ERROR: Error 1215: Cannot add foreign key constraint

y por más que miro todo lo que he encontrado:

  • mirar que las tablas estén escritas iguales (mayúsculas/minúsculas),
  • que las FK apunten a alguna Key (PK),
  • que sean del mismo tipo y contengan las mismas propiedades, etc

y no hay forma, a priori está todo ok. ¿veis vosotros algo que se me escape?. Os pongo los CREATE de las tablas que se ven afectados por este error:

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE TABLE IF NOT EXISTS `imagen`.`BOTELLA_CERVEZA` (
  `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
  `IMAGEN` BLOB NOT NULL,
  `ORDEN` TINYINT(0) UNSIGNED NOT NULL,
  `NOMBRE` VARCHAR(30) NOT NULL,
  `MIMETYPE` VARCHAR(10) NOT NULL,
  `TAMAÑO` FLOAT(10) UNSIGNED NOT NULL,
  `FECHA_ALTA` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID_BOTELLA_CERVEZA`, `ID_USUARIO`, `ORDEN`),
  UNIQUE INDEX `BOTELLA_CERVEZA_UK` (`ID_BOTELLA_CERVEZA` ASC, `ID_USUARIO` ASC, `ORDEN` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `imagen`.`VASO_CERVEZA` (
  `ID_VASO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
  `IMAGEN` BLOB NOT NULL,
  `ORDEN` TINYINT(0) UNSIGNED NOT NULL,
  `NOMBRE` VARCHAR(30) NOT NULL,
  `MIMETYPE` VARCHAR(10) NOT NULL,
  `TAMANYO` FLOAT(10) UNSIGNED NOT NULL,
  `FECHA_ALTA` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID_VASO_CERVEZA`, `ID_USUARIO`, `ORDEN`),
  UNIQUE INDEX `VASO_CERVEZA_UK` (`ID_VASO_CERVEZA` ASC, `ID_USUARIO` ASC, `ORDEN` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `cerveza`.`USUARIO_CERVEZA` (
  `ID_USUARIO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
  `ID_CERVEZA` INT(10) UNSIGNED NOT NULL,
  `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL,
  `ID_VASO_CERVEZA` INT(10) UNSIGNED NULL DEFAULT NULL,
  `PUNTUACION` TINYINT(0) UNSIGNED NULL DEFAULT NULL,
  `OPINION` VARCHAR(255) NULL DEFAULT NULL,
  `FECHA_COMPRA` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `FECHA_CONSUMO` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`ID_USUARIO_CERVEZA`, `ID_USUARIO`, `ID_BOTELLA_CERVEZA`),
  INDEX `USUARIO_CERVEZA_VASO_CERVEZA_IX` (`ID_USUARIO` ASC, `ID_VASO_CERVEZA` ASC),
  UNIQUE INDEX `ID_USUARIO_CERVEZA_BOTELLA_UK` (`ID_USUARIO` ASC, `ID_CERVEZA` ASC, `ID_BOTELLA_CERVEZA` ASC),
  CONSTRAINT `CERVEZA_USUARIO_FK`
    FOREIGN KEY (`ID_USUARIO`)
    REFERENCES `coleccion`.`USUARIO` (`ID_USUARIO`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_VASO_CERVEZA_FK`
    FOREIGN KEY (`ID_USUARIO` , `ID_VASO_CERVEZA`)
    REFERENCES `imagen`.`VASO_CERVEZA` (`ID_USUARIO` , `ID_VASO_CERVEZA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_BOTELLA_CERVEZA_FK`
    FOREIGN KEY (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
    REFERENCES `imagen`.`BOTELLA_CERVEZA` (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_CERVEZA_FK`
    FOREIGN KEY (`ID_CERVEZA`)
    REFERENCES `cerveza`.`CERVEZA` (`ID_CERVEZA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;

El error da en los dos constraint de la tabla USUARIO_CERVEZA siguientes:

      CONSTRAINT `USUARIO_CERVEZA_VASO_CERVEZA_FK`
        FOREIGN KEY (`ID_USUARIO` , `ID_VASO_CERVEZA`)
        REFERENCES `imagen`.`VASO_CERVEZA` (`ID_USUARIO` , `ID_VASO_CERVEZA`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `USUARIO_CERVEZA_BOTELLA_CERVEZA_FK`
        FOREIGN KEY (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
        REFERENCES `imagen`.`BOTELLA_CERVEZA` (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,

PRUEBA 1

Tras leer el comentario de @Josep, he probado a dejar en USUARIO_CERVEZA:

  • Como única PK “ID_USUARIO_CERVEZA”
  • Como UniqueKey el conjunto de “ID_USUARIO, ID_CERVEZA, ID_BOTELLA_CERVEZA”.
  • Como único FK la relación “ID_USUARIO y ID_BOTELLA_CERVEZA”, la otra relación la he eliminado para la prueba.

El resultado es el mismo error:

    Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        CREATE TABLE IF NOT EXISTS `cerveza`.`USUARIO_CERVEZA` (
          `ID_USUARIO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
          `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
          `ID_CERVEZA` INT(10) UNSIGNED NOT NULL,
          `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL,
          `ID_VASO_CERVEZA` INT(10) UNSIGNED NULL DEFAULT NULL,
          `PUNTUACION` TINYINT(0) UNSIGNED NULL DEFAULT NULL,
          `OPINION` VARCHAR(255) NULL DEFAULT NULL,
          `FECHA_COMPRA` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
          `FECHA_CONSUMO` DATETIME NULL DEFAULT NULL,
          PRIMARY KEY (`ID_USUARIO_CERVEZA`),
          UNIQUE INDEX `ID_USUARIO_CERVEZA_BOTELLA_UK` (`ID_USUARIO` ASC, `ID_CERVEZA` ASC, `ID_BOTELLA_CERVEZA` ASC),
          CONSTRAINT `CERVEZA_USUARIO_FK`
            FOREIGN KEY (`ID_USUARIO`)
            REFERENCES `coleccion`.`USUARIO` (`ID_USUARIO`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `USUARIO_CERVEZA_BOTELLA_CERVEZA_FK`
            FOREIGN KEY (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
            REFERENCES `imagen`.`BOTELLA_CERVEZA` (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `USUARIO_CERVEZA_CERVEZA_FK`
            FOREIGN KEY (`ID_CERVEZA`)
            REFERENCES `cerveza`.`CERVEZA` (`ID_CERVEZA`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8

PRUEBA 2

He eliminado la relación de ID_USUARIO con la tabla coleccion.USUARIO y ahora solo he dejado lo siguiente:

    Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        CREATE TABLE IF NOT EXISTS `cerveza`.`USUARIO_CERVEZA` (
          `ID_USUARIO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
          `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
          `ID_CERVEZA` INT(10) UNSIGNED NOT NULL,
          `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL,
          `ID_VASO_CERVEZA` INT(10) UNSIGNED NULL DEFAULT NULL,
          `PUNTUACION` TINYINT(0) UNSIGNED NULL DEFAULT NULL,
          `OPINION` VARCHAR(255) NULL DEFAULT NULL,
          `FECHA_COMPRA` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
          `FECHA_CONSUMO` DATETIME NULL DEFAULT NULL,
          PRIMARY KEY (`ID_USUARIO_CERVEZA`),
          UNIQUE INDEX `ID_USUARIO_CERVEZA_BOTELLA_UK` (`ID_USUARIO` ASC, `ID_CERVEZA` ASC, `ID_BOTELLA_CERVEZA` ASC),
          CONSTRAINT `USUARIO_CERVEZA_BOTELLA_CERVEZA_FK`
            FOREIGN KEY (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
            REFERENCES `imagen`.`BOTELLA_CERVEZA` (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `USUARIO_CERVEZA_CERVEZA_FK`
            FOREIGN KEY (`ID_CERVEZA`)
            REFERENCES `cerveza`.`CERVEZA` (`ID_CERVEZA`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8

Dando el mismo error

PRUEBA 3 Y OK

Modificando el conjunto de PK y añadiendo UniqueKey es como he podido solucionar mi problema. Pongo el código SQL correcto para la generación de las tablas por si ayuda en la aclaración:

    SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE TABLE IF NOT EXISTS `imagen`.`BOTELLA_CERVEZA` (
  `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
  `IMAGEN` BLOB NOT NULL,
  `ORDEN` TINYINT(0) UNSIGNED NOT NULL,
  `NOMBRE` VARCHAR(30) NOT NULL,
  `MIMETYPE` VARCHAR(10) NOT NULL,
  `TAMAÑO` FLOAT(10) UNSIGNED NOT NULL,
  `FECHA_ALTA` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID_BOTELLA_CERVEZA`, `ID_USUARIO`),
  UNIQUE INDEX `BOTELLA_CERVEZA_UK` (`ID_BOTELLA_CERVEZA` ASC, `ID_USUARIO` ASC, `ORDEN` ASC),
  UNIQUE INDEX `ID_BOTELLA_CERVEZA_UNIQUE` (`ID_BOTELLA_CERVEZA` ASC),
  UNIQUE INDEX `ID_USUARIO_UNIQUE` (`ID_USUARIO` ASC),
  UNIQUE INDEX `ORDEN_UNIQUE` (`ORDEN` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `imagen`.`VASO_CERVEZA` (
  `ID_VASO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
  `IMAGEN` BLOB NOT NULL,
  `ORDEN` TINYINT(0) UNSIGNED NOT NULL,
  `NOMBRE` VARCHAR(30) NOT NULL,
  `MIMETYPE` VARCHAR(10) NOT NULL,
  `TAMANYO` FLOAT(10) UNSIGNED NOT NULL,
  `FECHA_ALTA` TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID_VASO_CERVEZA`, `ID_USUARIO`),
  UNIQUE INDEX `VASO_CERVEZA_UK` (`ID_VASO_CERVEZA` ASC, `ID_USUARIO` ASC, `ORDEN` ASC),
  UNIQUE INDEX `ID_VASO_CERVEZA_UNIQUE` (`ID_VASO_CERVEZA` ASC),
  UNIQUE INDEX `ID_USUARIO_UNIQUE` (`ID_USUARIO` ASC),
  UNIQUE INDEX `ORDEN_UNIQUE` (`ORDEN` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `cerveza`.`USUARIO_CERVEZA` (
  `ID_USUARIO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO` INT(10) UNSIGNED NOT NULL,
  `ID_CERVEZA` INT(10) UNSIGNED NOT NULL,
  `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL,
  `ID_VASO_CERVEZA` INT(10) UNSIGNED NULL DEFAULT NULL,
  `PUNTUACION` TINYINT(0) UNSIGNED NULL DEFAULT NULL,
  `OPINION` VARCHAR(255) NULL DEFAULT NULL,
  `FECHA_COMPRA` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `FECHA_CONSUMO` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`ID_USUARIO_CERVEZA`),
  UNIQUE INDEX `ID_USUARIO_CERVEZA_BOTELLA_UK` (`ID_USUARIO` ASC, `ID_CERVEZA` ASC, `ID_BOTELLA_CERVEZA` ASC),
  INDEX `USUARIO_CERVEZA_VASO_CERVEZA_FK_idx` (`ID_VASO_CERVEZA` ASC, `ID_USUARIO` ASC),
  CONSTRAINT `CERVEZA_USUARIO_FK`
    FOREIGN KEY (`ID_USUARIO`)
    REFERENCES `coleccion`.`USUARIO` (`ID_USUARIO`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_BOTELLA_CERVEZA_FK`
    FOREIGN KEY (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
    REFERENCES `imagen`.`BOTELLA_CERVEZA` (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_CERVEZA_FK`
    FOREIGN KEY (`ID_CERVEZA`)
    REFERENCES `cerveza`.`CERVEZA` (`ID_CERVEZA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_VASO_CERVEZA_FK`
    FOREIGN KEY (`ID_VASO_CERVEZA` , `ID_USUARIO`)
    REFERENCES `imagen`.`VASO_CERVEZA` (`ID_VASO_CERVEZA` , `ID_USUARIO`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;

solución

Creo que el error es que estas usando los mismos campos para mas de una FK y eso diría que no es correcto. Ademas debes utilizar toda la PK de la tabla foránea en la FK.

Deberías crear un campo para cada FK para así poder hacer correctamente las claves foráneas. Te da error al crear las FK USUARIO_CERVEZA_VASO_CERVEZA_FK y USUARIO_CERVEZA_BOTELLA_CERVEZA_FK porque el campo ID_USUARIO ya existe en la FK creada anteriormente CERVEZA_USUARIO_FK.

La tabla podria quedar así (no tengo un MySQL aquí ahora pero creo que seria así si no me equivoco):

CREATE TABLE IF NOT EXISTS `cerveza`.`USUARIO_CERVEZA` (
  `ID_USUARIO_CERVEZA` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ID_USUARIO_USUARIO` INT(10) UNSIGNED NOT NULL,
  `ID_CERVEZA` INT(10) UNSIGNED NOT NULL,
  `ID_USUARIO_BOTELLA` INT(10) UNSIGNED NOT NULL,
  `ID_BOTELLA_CERVEZA` INT(10) UNSIGNED NOT NULL,
  `ORDEN_BOTELLA` TINYINT(0) UNSIGNED NOT NULL,
  `ID_USUARIO_VASO` INT(10) UNSIGNED NOT NULL,
  `ID_VASO_CERVEZA` INT(10) UNSIGNED NULL DEFAULT NULL,
  `ORDEN_VASO` TINYINT(0) UNSIGNED NOT NULL,
  `PUNTUACION` TINYINT(0) UNSIGNED NULL DEFAULT NULL,
  `OPINION` VARCHAR(255) NULL DEFAULT NULL,
  `FECHA_COMPRA` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `FECHA_CONSUMO` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`ID_USUARIO_CERVEZA`, `ID_USUARIO_USUARIO`, `ID_BOTELLA_CERVEZA`),
  INDEX `USUARIO_CERVEZA_VASO_CERVEZA_IX` (`ID_USUARIO_USUARIO` ASC, `ID_VASO_CERVEZA` ASC),
  UNIQUE INDEX `ID_USUARIO_CERVEZA_BOTELLA_UK` (`ID_USUARIO_USUARIO` ASC, `ID_CERVEZA` ASC, `ID_BOTELLA_CERVEZA` ASC),
  CONSTRAINT `CERVEZA_USUARIO_FK`
    FOREIGN KEY (`ID_USUARIO_USUARIO`)
    REFERENCES `coleccion`.`USUARIO` (`ID_USUARIO`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_VASO_CERVEZA_FK`
    FOREIGN KEY (`ID_USUARIO_VASO` , `ID_VASO_CERVEZA`, `ORDEN_VASO`)
    REFERENCES `imagen`.`VASO_CERVEZA` (`ID_USUARIO` , `ID_VASO_CERVEZA`, `ORDEN_VASO`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_BOTELLA_CERVEZA_FK`
    FOREIGN KEY (`ID_USUARIO_BOTELLA` , `ID_BOTELLA_CERVEZA`, `ORDEN_BOTELLA`)
    REFERENCES `imagen`.`BOTELLA_CERVEZA` (`ID_USUARIO` , `ID_BOTELLA_CERVEZA`, `ORDEN_BOTELLA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `USUARIO_CERVEZA_CERVEZA_FK`
    FOREIGN KEY (`ID_CERVEZA`)
    REFERENCES `cerveza`.`CERVEZA` (`ID_CERVEZA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *