Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.1.2
-
None
-
Ubuntu 14.04.1
Description
Proof of concept:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; |
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; |
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; |
-- -----------------------------------------------------
|
-- Table `shoes.tetsing.domain.com`.`shoes`
|
-- -----------------------------------------------------
|
CREATE TABLE IF NOT EXISTS `shoes.tetsing.domain.com`.`shoes` ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`name` VARCHAR(45) NULL, |
PRIMARY KEY (`id`)) |
ENGINE = InnoDB;
|
-- -----------------------------------------------------
|
-- Table `shoes.tetsing.domain.com`.`colors`
|
-- -----------------------------------------------------
|
CREATE TABLE IF NOT EXISTS `shoes.tetsing.domain.com`.`colors` ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`color` VARCHAR(45) NULL, |
PRIMARY KEY (`id`)) |
ENGINE = InnoDB;
|
-- -----------------------------------------------------
|
-- Table `shoes.tetsing.domain.com`.`shoes_colors`
|
-- -----------------------------------------------------
|
CREATE TABLE IF NOT EXISTS `shoes.tetsing.domain.com`.`shoes_colors` ( |
`id` INT NOT NULL AUTO_INCREMENT, |
`shoe_id` INT NOT NULL, |
`color_id` INT NOT NULL, |
PRIMARY KEY (`id`), |
INDEX `fk_shoes_colors_shoes_idx` (`shoe_id` ASC), |
INDEX `fk_shoes_colors_colors1_idx` (`color_id` ASC), |
CONSTRAINT `fk_shoes_colors_shoes` |
FOREIGN KEY (`shoe_id`) |
REFERENCES `shoes.tetsing.domain.com`.`shoes` (`id`) |
ON DELETE CASCADE |
ON UPDATE CASCADE, |
CONSTRAINT `fk_shoes_colors_colors1` |
FOREIGN KEY (`color_id`) |
REFERENCES `shoes.tetsing.domain.com`.`colors` (`id`) |
ON DELETE CASCADE |
ON UPDATE CASCADE) |
ENGINE = InnoDB;
|
SET SQL_MODE=@OLD_SQL_MODE; |
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; |
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
INSERT INTO `shoes.tetsing.domain.com`.`shoes` (`name`) VALUES ('shoe 1'); |
INSERT INTO `shoes.tetsing.domain.com`.`colors` (`color`) VALUES ('black'); |
INSERT INTO `shoes.tetsing.domain.com`.`shoes_colors` (`shoe_id`, `color_id`) VALUES (1, 1); |
Error in CONSTRAINT REFERENCES ... Can not insert.
The problem is:
REFERENCES `shoes.tetsing.domain.com`.`shoes` (`id`)
replace by:
REFERENCES `shoes` (`id`)
And it works!