lundi 27 juin 2016

Cannot add foreign key #1215

I am getting the error saying '#1215 Cannot add foreign key' while trying to forward engineer from mysql workbench.

I have a rectangle table which references columns in scales and entities table. The create rectangle statement throws the error.

This is my schema

-- -----------------------------------------------------
-- Table `newDb`.`Scales`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `newDb`.`Scales` (
  `idScales` INT NOT NULL AUTO_INCREMENT,
  `scale_name` VARCHAR(100) NULL,
  `pid` INT NULL,
  `col_Id` INT NULL,
  `type` VARCHAR(45) NULL,
  `range_from` INT NULL,
  `range_to` INT NULL,
  `bandpadding` INT NULL,
  PRIMARY KEY (`idScales`),
  UNIQUE INDEX `idScales_UNIQUE` (`idScales` ASC),
  INDEX `pid_idx` (`pid` ASC),
  INDEX `col_id_idx` (`col_Id` ASC),
  CONSTRAINT `pid`
    FOREIGN KEY (`pid`)
    REFERENCES `newDb`.`projects` (`pid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `col_id`
    FOREIGN KEY (`col_Id`)
    REFERENCES `newDb`.`data_sets_columns` (`col_Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `newDb`.`Entities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `newDb`.`Entities` (
  `idEntities` INT NOT NULL AUTO_INCREMENT,
  `entity_name` VARCHAR(100) NULL,
  `entity_type` VARCHAR(45) NULL,
  `pid` INT NOT NULL,
  PRIMARY KEY (`idEntities`),
  UNIQUE INDEX `idEntities_UNIQUE` (`idEntities` ASC),
  INDEX `pid_idx` (`pid` ASC),
  CONSTRAINT `pidEF`
    FOREIGN KEY (`pid`)
    REFERENCES `newDb`.`projects` (`pid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `newDb`.`Rectangle`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `newDb`.`Rectangle` (
  `idRectangle` INT NOT NULL AUTO_INCREMENT,
  `rect_name` VARCHAR(100) NULL,
  `X_pos` INT NOT NULL DEFAULT 0,
  `Y_pos` INT NOT NULL DEFAULT 0,
  `Height` INT NOT NULL DEFAULT 50,
  `Width` INT NOT NULL DEFAULT 50,
  `Offset_X` INT NOT NULL DEFAULT 0,
  `Offset_Y` INT NOT NULL DEFAULT 0,
  `Opacity` INT NULL DEFAULT 100,
  `Color` VARCHAR(10) NOT NULL DEFAULT 'black',
  `idScale` INT NOT NULL,
  `idEntities` INT NOT NULL,
  `idColorScale` INT NULL,
  PRIMARY KEY (`idRectangle`),
  UNIQUE INDEX `idRectangle_UNIQUE` (`idRectangle` ASC),
  INDEX `idScales_idx` (`idScale` ASC, `idColorScale` ASC),
  INDEX `idEntities_idx` (`idEntities` ASC),
  CONSTRAINT `idScalesF`
    FOREIGN KEY (`idScale` , `idColorScale`)
    REFERENCES `newDb`.`Scales` (`idScales` , `idScales`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `idEntitiesF`
    FOREIGN KEY (`idEntities`)
    REFERENCES `newDb`.`Entities` (`idEntities`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

There is another question: MySQL Error 1215: Cannot add foreign key constraint asking for the same thing.

I followed it and according to the answer I am either having mismatched engine, mismatched column type or mismatched columns.

I don't seem to see any mismatches though as all my referenced columns and foreign key coloumns are unsigned INTs ,the name is same, and the engine is INNODB.

Can't seem to find what I am missing here

Aucun commentaire:

Enregistrer un commentaire